I am trying create some code that checks if the contents of the status field in a table is "Authorised" where the ID = something specific for multiple records. If all records witht the specific ID are "Authorised" Then generate a new record in another table. Where to start, perhaps a Dlookup?
View RepliesI have created code to import and excel file and create a table from that info. Now I need to confirm that the import has the correct Datatype of Number and Fieldsize of Double for one of the columns.
View 2 Replies View RelatedI am trying to perform a search function query in access but am having trouble. I have two tables. One table has a column in which I need to search the contents of this field by referencing data in another field/table. I am able to write the query searching the data manually (not referencing the other table) but can't seem to get the query right when referencing the other table!
this query works =
SELECT *
FROM Sample_Data
WHERE (((Sample_Data.[Affected_Frequencies]) Like "*451.425*" Or (Sample_Data.[Affected_Frequencies]) Like "*451.400*"));
but, i put together a frequency table (Table name = Frequencies) and the column within that I am searching for is called Frequency. im trying to reference Frequencies.Frequency using a LIKE statement.
I have a table that stores ID,PolicyNumber,NewPolNo fields.It has millions of records. The field NewPolNo is empty. I need vba code that will check the following for each record:
1. If the length of the PolicyNumber is over 14 characters long then store that PolicyNumber value in NewPolNo field.
2. If the length of the PolicyNumber is less than 14 characters long thenagain store that PolicyNumber value in NewPolNo field.
3. But if the length of the policyNumber equals to 14 digits long then check the following:
a) If the last 7 digits are 0's of that PolicyNumber. If yes then store first 7 digits in NewPolNo field.
b) If the last 7 digits are not 0's then store the whole PolicyNumber value in NewPolNo field.
We need to check these conditions for each record of the table.
I am designing a project in which there is a form and on that form there is a textbox and Browse button. With browse button you can select a file from the dialog box and that file path will appear in the textbox. This part has been done as below:
Code:
Private Sub CommandButton1_Click()
ChooseFile
End Sub
Sub ChooseFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
[Code] ....
Now the next Part i.e Part2 in which we have to see the references present in Column D of the "Summary" worksheet of the workbook whose path is present in textbox1 and check if they match with any of the references in field "Ref" of the Access table named tblLiterature.
If value in column D matches with Ref field of the Access table then change the status field of the Access table to the corresponding columns A,B,C named as "Withdrawn","Obsolete","Updated". that means if the column A of the corresponding Reference is Y then change the status field to "Withdrawn". If column B is Y then change the status to "Obsolete" and if column C is Y then change the status to "Updated".
Please see attached the workbook as this kind of workbook will be searched against Access table .
I need to use an iif statement to search a field (column) for a certain word. The field is in tblEquipmentName and the input is in tblWorkReport
Ex.
iif(input from tblWorkReport is in field from tblEquipmentName,...,...)
I've got some code that loops through a record and copies the non-null fields to another record. This is the partial code:
For Each fld In tdf.Fields
If Not IsNull(Forms!contact_lookup![Contact_sub subform1].Form(fld.NAME)) Then
strSql = "UPDATE tbl1 SET tbl1.[" & fld.NAME & "] = '" & Forms!contact_lookup![Contact_sub subform1].Form(fld.NAME) & "' " & _
"WHERE tbl1.[FC_APN] = '" & Me.txtApn & "';"
DoCmd.RunSQL strSql
End If
Next fld
This works fine until it encounters a field whose contents has a reserved character, like a single quote (" ' ") in it, for example, "What's the what." At that point the SQL thinks there's a " ' " missing in the statement, and I get an error 3075 (syntax error: missing operator).
I am very new to access less than 1 week since i started trying to build an horse racing database, i am trying to link data from 2 different sources via the horses name however one source displays this with the horses country of origin in parenthesis foe example FRANKEL(GB) and the other source displays the name as just FRANKEL, to be honest i haven't yet tried the link but guess it will fail.
I am therefore looking to get rid of the parentheses and their contents from an access table field and create another field without them.
In Excel i use the formula B1:
=TRIM(REPLACE(A1,FIND("(",A1&"("),FIND(")",A1&")")-(FIND("(",A1&"(")-1),"")) and that works fine.
I guessed the solution for Access would be newname:
TRIM(REPLACE([frhorse.NAME],FIND("(",[frhorse.NAME]&"("),FIND(")",[frhorse.NAME]&")")-(FIND("(",[frhorse.NAME]&"(")-1),""))
But this doesn't work as in returns undefined function "FIND" in expression error.
I have fairly large table called QBInvoices that consist two fields- qbinv and date . Also, I have a report that showing info off query called TotalQueryQB. The table and query have no relations except dates . I need to show on report filed qbinv off table QBInvocies on the top of the reprot based on date. The user type date on form -frmQB text box txt Date and I would like to have contest of field qbinv off table QBInvocies on the top of the report base on date that was typed.
View 2 Replies View RelatedHi,
I'm making a form where the contents of a field is determined on the contents of another field in another form. I thought an IIF function would work, but when I tried it the contents says #NAME.
I put it in the control source.
IIF([Forms]![Frm_NewBusiness]![Page4]![Child51]="NTU",NewBusiness_Date_Issued="NTU",NewBusiness_Date_Issued)
Any ideas?
Cheers,
Ben
I have a situation where I have a report which I will generate to print our companies pricebook. I need an index/table of contents for customers to go to the page where a product is to look up prices. The problem is that we add and subtract products on a consistent basis.
So a new product will get added, but of course, to run alphabetically, there's no way to insert it, then renumber the pages for it to be easily found.to use a maketable query, which sorts all of the products alphabetically. Each time it is run, it will delete the previous table. I then need to have an autonumber field created to create my "page numbers". Yes, each time the pricebook is run, products can and will oftentimes get new page numbers.
So my thought is to have a macro run which creates the autonumber field to the table. This will then be the basis for the form, which will in turn utilize the "page number"/autonumber field to both serve as page numbers in the report. And of course, the table which is created each time will be the "table of contents".
I have a report that has multiple fields - 3 of which matter for this discussion -
1.) employee
2.) employer
3.) rateofpay
The employer field contains 1 of 2 options. For the sake of this conversation, lets call it ABC123 and XYZ123
I need to be able to calculate the average rate of pay for ABC123 company, and exclude the rate of pay for XYZ123 company in my report.
I am calculating this in a section footer.
i have multpile yes/no check box i want if i check on one the name of this check box is written on another one.
so i neeed a code for loop of check boxes and i need a code i.e loop all check boxes if any is true then put name field in {new field}
What is wrong with this criteria...
RecSet.FindFirst ("[SomeField] = '" & mMESTO & "'") and ([CheckField] = True)
I have a code that finds the first, second, and third minimum value in a row across the fields. Now, I am trying to find a code to look at these values, find the field it is located, and return the field name. I tried several variations of my code to return the field name rather than the value, but have been unsuccessful to this point.
Function NthMinimum(intPosition As Integer, ParamArray FieldArray() As Variant) As Variant
Dim varTempArray() As Variant, varTempValue As Variant, intArrayValues As Integer
Dim I As Integer, J As Integer
ReDim varTempArray(UBound(FieldArray))
intArrayValues = 0
[Code] ....
As you can see, this works to find these values while ignoring NULLS. How to return the field name?
I have a field within a form that needs to be modified based off of someone's access level and I have written that part but not sure how to keep others from modifying the field.
Code:
Private Sub Qty_Rcvd_Click()
If ap_GetUserName() = "Danny_Davis" Or ap_GetUserName() = "christopher_ayers" Or ap_GetUserName() = "Tena_McCrackin" Then
GoTo 10
Else
MsgBox "You are not authorized to view this form"
End If
10
End Sub
Right now this code will bring up the msgbox for someone not authorized but once they hit "ok" the msgbox goes away and they can still change the field. What can I type in here to lock down the field for someone not authorized?
I am using MS Access 2010 and I am struggling to write some vba in my database. On my form, I have a Payment Type dropdown field.When someone selects a payment type I want the code to calculate the end date. Then populate the End Date field with the answer.I will be having a few payment types too, but for now on the dropdown I have two.2/Month and 24/Annum.
View 3 Replies View RelatedI am storing values of pictures and the location of them in a table, this works fine!... using OpenRecordset. The problem is that when the function is called to store the information, it just keeps adding the same values of each file in the folder over and over again in a word "Duplicating" the information.
I have tried various methods using the OpenRecordset, but cannot seem to find the correct manor of applying the code.
Below is the function I have for storing the data...
Code:
Public Sub GetFilesNamesFromFolder(strFolderPath As String)
On Error GoTo ErrorHandler
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.folder
Dim objFile As Scripting.File
[Code] .....
Our quotation tool, which is built in access 2003, has a memo field, which is extracted in a .txt file, to be uploaded by another system.
When the user starts the memo field with a blank line, by hitting the enter key, the upload file writes the memo field as a new line, separating it from the line indicator and thus giving errors.
My question would be : How would I determine in VBA, whether the first line of the memo field is blank.
I am writing the following code to check if 'Active' field in table TypeTable2 is ticked or not for records. If I write the following statement to select the records from Access table where Active is not ticked then it gives "data Type mismatch in criteria expression".
Code:
strsql = "SELECT distinct EnvelopeType FROM TypeTable2 where Active='Yes'
convert the following into VBA code for an Access 2007 field on a form. It is needed to create a conditional default value based on another field's category: (Note: TransType, MembershipYear and Dues are field names and are all on the form). Based on the TransType (which is really a category of membership) I want the Dues field to have the applicable default value automatically entered.
For MembershipYear > 2008
If TransType="Individual" or TransType="I" Dues=35.00
If TransType="Family" or TransType="F" Dues=50.00
If TransType="Founder" Dues=100.00
If TransType="Student" Dues=10.00
If TransType="Lifetime" Dues="" or is Null
If (TransType is Null or TransType="") and TotalPaid >0, Dues=35.00
I need to add a Yes/No field to a table. I know I can do this in design view. Next I want to loop through the table and set it to Yes if certain fields are null. What I need help with is the VBA looping part and setting the boolean field to Yes if the fields being checked are null.
View 3 Replies View RelatedI have one query (lets call i Q1) and I have two tables with identical structure.
Table 1:
- id
- iddesc
- comment
Table 2
- id
- iddesc
- comment
Table 1 consists of an imported xslx-file. The idea with Table 2 is to use it to edit content. Sometimes only one field is edited in Table 2 and in those cases i need to get the other values from Table 1. Q1 should (but doesnt) generate something like this:
Q1:
- id [always from Table 1]
- iddesc [from Table 2]
- comment [from Table 1]
My question: How do i, in a query, check if data exists in a field in Table 2 and if it does, add data from that field and not from Table 1?
The DolphinBatchNo has number data type but the following sql statement doesn't capture the ight records. it doesn't check id dolphinBatchNo is blank.
Code:
strsql="select * from `MasterTBL` where PolicyNumber>=" & TxtFPolNo & " and PolicyNumber<=" & TxtLPolNo & " and PolicyStatus='Live' and DolphinBatchNo is null order by PolicyNumber"
Using Access 2010. Fairly new to automation and macros.I have two tables (tblProductReceived and tblBins) and a form (frmProductReceived).A field in tblBins corresponds to a location the bin is at (named BinLocationID) and the tblProductReceived table tracks product that a specific bin has received.
What I need is for the tblProductReceived field PRLocationID ([tblProductReceived].[PRLocationID]) to be automatically populated with where the bin is at ([tblBins].[BinLocationID]) when selecting a specific bin in the form (frmProductReceived).
I'm certain this question has probably been asked before, but I can't seem to find it!
I have a form field called fldTitle, and want to ensure users write something that is meaningful by evaluating words within the title to a table of keywords (tblKeywords). I know how to write the IF/ENDIF and the other stuff required, but am struggling to find the right commands to do the comparision. Could someone help me out?
Many thanks.