The following code works fine
G_StrWhere = "[Group]=" & G_Group_Code & " AND " & "[EntryDate]=" & SQLDate(G_Greg_Date)
Me.Filter = G_StrWhere
Me.FilterOn = True
I would like to get 2 Groups together in the first part using the syntax " IN"
e.g. (4,6 IN G_Group_Code)
If I type 2014 instead of b everything works out.
Code:
Dim a As Double
Dim b As String
b = Right(RepMonth, 4)
a = DLookup("[EUR/TRY]", "Plan_FX_RATES", "YEAR = '" & b & "'")
Debug.Print a
Debug.Print b
Debug.Print RepMonth
I'm experiencing a error when I go to add a filter, "Syntax error in string in query expression "MyFieldName""..If I go to to the table where the field is located, I CAN apply a filter.However, If I throw this single field on a form and switch to datasheet view, all of a sudden I can not filter it.
Additional info: whenever I go to build a query with the field, it throws brackets around this field and no others... this is weird.
Code:
SELECT MyTable.field1, MyTable.field2, MyTable.[problemfield]
FROM MyTable
I'm trying to transfer records from a table in one subform to another subform (on same form) using a checkbox as a filter:
Private Sub TransferAndReview_Click()
Dim db As dao.Database
Dim strSQL As String
strSQL = "INSERT INTO Forms![Extra Work Report Checksheet]![Equipment Input Subform1].Form[(QuantityUsed[, HoursUsed])] VALUES (QuantityUsed[, HoursUsed]) FROM Forms![Extra Work Report Checksheet]![Equipment Checksheet Table Subform] WHERE [Extra Work Report Checksheet]![Equipment Input Subform1].Form[EquipmentUsed]=True;"
Set db = CurrentDb
db.Execute strSQL, dbFailOnError
End Sub
The Main form is: [Extra Work Report Checksheet]
Subforms are: [Equipment Input Subform1] & [Equipment Checksheet Table Subform]
Checkbox field is: [EquipmentUsed]
I keep getting the error "Syntax error in INSERT INTO statement" which points to 'db.Execute strSQL, dbFailOnError' and I don't understand why. Neither of the fields [QuanityUsed] or [HoursUsed] are lookup fields, etc.
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code:
'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they wish, _
and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
I have a continuous form based on table "INCOMES" that shows all the payments received, which mediums can be (field "PMNT_MEDIUM"):
- check
- transfer
- taxes
- cash
Table "INCOMES" is filled using another form, but in this particular form I just want to show "check", "transfer" and "cash" (not "taxes") so that I can track all the cash incomes.
Note: taxes are loaded because they appear in my invoices and I need them there to reach the invoice total amount.So my form has a search bar which allows me to search by PMNT_MEDIUM listing all "checks", all "cash" or all "transfer". I can also search by payment number (meaning: check number). To that end I have a "search" button that applies the filter. And I have another button that "cleans" the filtering by "putting a "" in the search-bar and then calling the "on click" of the search button".
What I need is, no matter if I click over the "search" or "clean" button, it NEVER shows me the "taxes".Search button, on click code:
If IsNumeric(Me.busq_chq_med) Then
Me.Filter = "[PMNT_MEDIUM_NUMB] =" & Me.SEARCH_BAR
Else
Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*'"
Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*' or [INVOICE] like'" & Me.SEARCH_BAR & "*'"
End If
Me.FilterOn = True
Clean filter button, on click code:
[SEARCH_BAR] = ""
Call [Search button]_click
Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*'"
Me.FilterOn = True
I have a form that is showing data from 1 table. That table has 12 different fields on it and I want to be able to filter based on selections I make in a combo box in the header of the form. The filter string must be dynamic enough to allow filtering based on 1 criteria selected, or multiple criteria selected. For example:
If I have values in filter fields 3, 5, and 9 I'd want the filter string to be created as follows:
"...WHERE field3 = field3filter.value AND field5 = field5filter.value AND field9 = field9filter.value"
If I have values in only field 7, I'd want th efilter string to be created as follows:
"...WHERE field7 = field7filter.value"
And so on and so on.
I have created some filters before but all of the different VBA syntaxes I'm using seem to come up short.
How can I create a "Filter Button" on a form and filter my records? I create a textbox on a form and a filter button on the right. Then I click the filter futton, the filter function will search/match the content in the box through the datasheet. And then the results of the filtering will be pop up on the split form datasheet.
View 3 Replies View RelatedI am using MS Acess2000 and need to make a report that will be passed around with production work. My primary key is the invoice number of the work order. Currently to open an invoice i have a macro, attached to a query with the following qualifiers; Like [Enter invoice]
This pulls up the current record fine.
for the form and flags the folloing in the property filter sectin of the form
(((([CustomerTableMasterRef].[Invoice]) Like [Enter invoice])))
PART 2
Now I am trying to use microsofts how to filter a report using a forms filter...
This picks up on the Invoice query as shown above but does not just insert the query results...
Is there better code or another way to approach this... Currently i am using:
Name:cmdOpenReport
Caption: Open Report
OnClick: [Event Procedure]
Private Sub CmdOpenReport_Click()
If Me.Filter = "" Then
MsgBox "Open an Invoice First"
Else
DoCmd.OpenReport "rptCustomers", acViewPreview, , Me.Filter
End If
End Sub
Using this code not only does my report not detect the correct fields to import data (no data is filled in) but it requerys the invoice or atleast should, which I could do with out all of that code...
Where should i go from here?
I have placed a filter button on a form as a filter and written the following on-click event procedure:
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdApplyFilterSort
Me.Filter = "ACCOUNT_DO_NOT_EMAIL = 'HS'"
Me.FilterOn = True
When I click the filter button I get a blank message box titled 'Microsoft Access" and an OK button, when closed the filter works perfectly.I have checked this procedure in other forms and it works without showing the blank message box.The only difference with this form is that its control source is a union query.
Hi,
in the following code:
Dim dbtmp As DAO.Database
Dim tblObj As DAO.TableDef
Dim rs As DAO.Recordset
Set dbtmp = OpenDatabase("C:WINDOWSBureaudevcli.xls", False, True, "Excel 8.0;")
DoEvents
Set rs = dbtmp.OpenRecordset("select * from [DEVCLI$A1:C10]")
in the last line i would like replace DEVCLI (the Excel sheet name) by a variable to make my program works with any excel file.
What is the syntaxe for that ?
Thanks in advance for help.
VINCENT
Think my syntax is wrong here, but not sure where. I would appreciate any help! Thanks!
SELECT a.[FA Ctr], a.[SAP Co], a.[SAP Ctr], a.[GL Co],
IIf([Len([SAP CO])] ="3",""0"' & [a].[SAP CO]", IIf([Len([SAP CO])]="2",""'00"' & [a].[SAP CO]"), IIf([Len([SAP CO])]="1",""'00"' & [a].[SAP CO]", "[A].[SAP CO]"))) AS Expr3
INTO [Interim Table]
FROM Asset_Map AS a
Hi there
I'm trying to write a query using an IIF statement in the "Field" property of the Access Query Design Grid the new Column is called F:
there are 3 Columns, System, Etot, Itot in a mytable
Basically in English the query goes,
IF mytable.[System] = 700,
mytable.[Etot] = 0,
mytable.[Itot] = 0
then the newly created derived column
mytable.[F] "N/A"
OTHERWISE
mytable.[F] "OK"
I hope someone understands,
Batwings
Hi,
I wud like to know why this syntax is not working.
select * from table_name where col_val like 'a%'
This returns the column values that start with 'a'. I was getting the result properly few days ago. But now suddenly this query doesnot give the result. When i tried this out in a website it was working there, but not in access.
Can anybody see what is wrong in this syntax? If the syntax is right where wud be the fault?
Can someone tell me how to insert the contents of a variable into a table?
I am trying to add selected items from a listbox (Members) into a temporary table (tempMembers). The code is below. On debugging, it picks up the contents of the variable (iMemberID) OK, but adds "0" to the temp table (both are integers). I'm pretty sure the problem is in the Insert statement below (stSQL3), but I can't figure out how to write it!
Any help would be greatly appreciated!
Thanks
stSQL3 = "INSERT INTO tempMembers (MemberID) VALUES & iMemberID & ;"
For Each varItm In Me!Members.ItemsSelected
iMemberID = ctl.ItemData(varItm)
DoCmd.RunSQL stSQL3
Next varItm
Can anyone tell me the proper syntax for:
1. Calling another Form from within a Form
2. Calling a module from within a Form
Thanks in Advance
Can someone help me on the syntax for the following?
I have two tables: one is TapeNumber and has one field (Tape_No) autonumbered PK. The other table has information about the video and uses the Tape_No as a secondary key.
I have a form to add information about videos. I have the form bound to the first table, so it adds a new TapeNumber when I add a record. I have a subform displaying the video information for that tape (there may be several videos recorded on one tape).
When I add a new tape (or video to an existing tape), how do I save the tape number to the second table?
Thanks!
have this code which keeps on giving me this error...I dont know whats wrong with the code please help tried removing the quotes (rstStudents.Open "SELECT * FROM Students WHERE Regno = " & _
txtReg , _
CurrentProject.Connection, _
adOpenStatic, adLockReadOnly, adCmdText) but then it wont retain any records even if they exist in the table
here is the original code
rstStudents.Open "SELECT * FROM Students WHERE Regno = '" & _
txtReg & "'", _
CurrentProject.Connection, _
adOpenStatic, adLockReadOnly, adCmdText
ERROR MESSAGE>>>>>>> Runtime Error '-2147217913 (80040e07)
Data type mismatch in criteria expression
the code is supposed to use number input by user to search in a table and fill all the other fields in the form with the table data (it is run on lost focus of the text box were value has been input)
the whole code :::::
Private Sub txtReg_LostFocus()
Dim rstStudents As ADODB.Recordset
Dim blnFound As Boolean
Dim fldItem As ADODB.Field
blnFound = False
If Me.txtReg = "" Then Exit Sub
Set rstStudents = New ADODB.Recordset
rstStudents.Open "SELECT * FROM Students WHERE Regno = '" & _
txtReg & "'", _
CurrentProject.Connection, _
adOpenStatic, adLockReadOnly, adCmdText
With rstStudents
While Not .EOF
For Each fldItem In .Fields
If fldItem.Name = "Regno" Then
If fldItem.Value = txtReg Then
Me.txtName = .Fields("Name")
Me.txtAdd = .Fields("Address")
Me.txttel = .Fields("Telno")
Me.txtTutor = .Fields("TutorName")
Me.txtbks = .Fields("NoBooksonloan")
blnFound = True
End If
End If
Next
.MoveNext
Wend
End With
If blnFound = False Then
MsgBox "No student record to display"
' ... and reset the form
' cmdReset_Click
End If
rstStudents.Close
Set rstStudents = Nothing
End Sub
I am trying to open a PDF from Access. I get it to work but it requires me to hard code the path and fiel name....
The path will always be the same although the file name will change. RIght now I get the fiel name from a textbox on a form...I am trying to change the below working code with a varaible instead of the path....I think there is some systax issues that I cannto fingure out
TotPath is the variable that holds the entire path to the file "X:Map_LibraryPark_and_Rec_AsbuiltPRA_19_73-01_W0.pdf"
This variable is rebuilt everytime the user selects something on the form....so the actual File name will change every time...
Does anyone have and ideas as to how to incorporate a variable instead of the hard coded path?
THank you all for your help.....
Code:WORKING CODE: 'Shell "C:Program FilesAdobeAcrobat 7.0AcrobatAcrobat.exe X:Map_LibraryPark_and_Rec_AsbuiltPRA_19_73-01_W0.pdf", vbMaximizedFocus ' open a txt documentNOT WORKING CODE:Shell "C:Program FilesAdobeAcrobat 7.0AcrobatAcrobat.exe TotPath", vbMaximizedFocus ' open a txt document
I'm familiar with SQL but not with Access97 - some wierd syntax here!
I'm creating a Query that is to Delete records from a table depending on a non-key field in a second table.
I first tried an INNER JOIN
DELETE Activity_Preferences.*
FROM Activity_Preferences INNER JOIN Activity_Key
Activity_Preferences.Activity_Key=Activity_Key.Act ivity_Key AND Activity_Key.Complete=1;
which was initially accepted but then complained about the INNER JOIN after I went in a second time to 'fix' the error.
I've finally got the following syntax accepted:-
DELETE Activity_Preferences.*
FROM Activity_Preferences, Activity_Key
WHERE Activity_Preferences.Activity_Key=Activity_Key.Act ivity_Key AND Activity_Key.Complete=1;
But when executed still gets the following error (same as for the INNER JOIN):-
'Operation must use an updatable query'.
What else is needed?
J.
:confused:
Hi,
In a form i use the following filter fonction :
DoCmd.ApplyFilter , "CliNom = '" & RunCli & "'"
The problem is that sometimes RunCli (wich is a name) contains ' symbol and then generates a syntaxe error, is there a way to prevent that ?
Thanks in advance.
VINCENT
On my form I have 2 radio buttons rdoAll and rdoSpecific. If rdoAll is true then it prints a report. THis part works fine. However if rdoSpecific is true then I make visable combo box to look up an ID. Then when I click the button I want the same form to open but with just the info pertaining to the ID selected. Here is my code:
Private Sub cmdLotHistory_Click()
Dim stDocName As String, stSelection As String
stDocName = "rptLotHistory"
If Me.rdoAll = True Then
DoCmd.OpenReport "rptLotHistory", acViewPreview
End If
If Me.cboLotLU.Value > 0 Then
stSelection = "[LotID] =" & Me![cboLotLU]
End If
DoCmd.OpenReport stDocName, acViewPreview, , stSelection
End Sub
When I choose the ID and click the button I get the message
Run-Time error '3075':
Syntax error (missing operator) in query expression '(LotID
=020806B1585)'.
the 020806B1585 is my ID number.
Debug hightlights the
DoCmd.OpenReport stDocName, acViewPreview, , stSelection
but I'm guessing my real problem lies in the
stSelection = "[LotID] =" & Me![cboLotLU]
Can anyone point me in the right direction?
Thanks,
Rick
I am using the folling code in a after update combobox event:
Dim rst As DAO.Recordset
Dim sqlwhere As String
sqlwhere = " "
If IsNull(Me.comboSearchSerial) = False Then
sqlwhere = "SerialNumber='" & Me.comboSearchSerial & "' and "
End If
mysql = "SELECT TBL_RMA.* from [TBL_RMA] Where "
mysql = mysql & sqlwhere
mysql = mysql & ";"
Forms!FRM_RMA.RecordSource = mysql
Exit_comboSearchSerial_AfterUpdate:
This is currently working for me with SerialNumber being a text field. I would like to use the same code substituting SerialNumber with a Date field. I know its probly an issue with the use of quotes, but I can't seem to figure it out. Also, if I wanted to use a number format, what would I use...
Thanks
Gregg
I am receiving this error;
"Invalid SQL syntax, cannot use multiple columns in a column level CHECK constraint."
Without explaining my disaster of a situation anyone have any ideas?
Hi, I need some syntax help on the following line of code:
rs.FindFirst (rs![NewRecord] = True And IsNull(rs![Event_No]))
where NewRecord is type boolean and Event_No is string. Ive used this function mainly on strings so am not sure what to do with this one!
thanks!!
I have a FE/BE access database. I have split it for our business processes from the original owners who decided to keep it combined. I have in my code Set rst = CurrentDb which needs to point to the BE (where the tables are) and I am struggling with getting the proper syntax. My error msgs are as follows:
"Operation is not supported for this type of object" (most likely becuase the rst is nothing)
I then get an error msg related to the form that it cannot update the StudentID field.
The help VB help module is not able to display this information..
Any help is appreciated.
Thanks