Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
use button to open query
- Thread starter pbrown99
- Start date
Your filter would actually be in the query. The other option is to use DoCmd.RunSQL and paste your SQL statement.
Option 1:
Your criteria in your query should look something like...
[Forms]![Form1]![Field1]
or
Option 2:
Your VBA code should look something like...
DoCmd.RunSQL "SELECT & FROM Test WHERE Field1='" & text1 & "'
Option 1:
Your criteria in your query should look something like...
[Forms]![Form1]![Field1]
or
Option 2:
Your VBA code should look something like...
DoCmd.RunSQL "SELECT & FROM Test WHERE Field1='" & text1 & "'
- Thread starter
- #3
Okay, now I am lost.
The following code works great for opening the report but if I use the SQL or even the query where would the "Buildwhere (me," Or ")" go?
The below opens the report with the correct dates.
DoCmd.OpenReport "Summary Time and Attendance Register", acViewPreview, , BuildWhere(Me, "OR")
The problem is that on a rather regular basis, I need to delete a day for some reason or another, therefore I was hopeing to use a delete query with the Buildwhere(me,"Or")
The form is set up to allow me to enter upto 4 different date ranges and/or 3 different individual days. (That comes in handy around Holiday season).
Any idea of how to formulate the SQL or Delete Query? Currently there is the "Code" from the query...
DELETE DISTINCTROW [TEST D].[EFF-DTE], [TEST D].[CO-NBR], [TEST D].[CC-NBR], [TEST D].ROLL, [TEST D].[LOA-HRS], [TEST D].[VAC-HRS], [TEST D].[NCDE-HRS], [TEST D].[OTHR-HRS], [TEST D].[SCH-RG], [TEST D].[SCH-OT], [TEST D].[SCH-DT], [TEST D].[BOR-RG], [TEST D].[BOR-OT], [TEST D].[BOR-DT], [TEST D].[LOAN-RG], [TEST D].[LOAN-OT], [TEST D].[LOAN-DT], [TEST D].[WKO-RG], [TEST D].[WKO-OT], [TEST D].[WKO-DT], [TEST D].[CC-DSC]
FROM [TEST D]
WHERE ((([TEST D].[CC-NBR])<>9999));
As you can see I am using a "Test" table, however the field names are correct, as well as not deleting "9999".
Thank you for any and all help!
The following code works great for opening the report but if I use the SQL or even the query where would the "Buildwhere (me," Or ")" go?
The below opens the report with the correct dates.
DoCmd.OpenReport "Summary Time and Attendance Register", acViewPreview, , BuildWhere(Me, "OR")
The problem is that on a rather regular basis, I need to delete a day for some reason or another, therefore I was hopeing to use a delete query with the Buildwhere(me,"Or")
The form is set up to allow me to enter upto 4 different date ranges and/or 3 different individual days. (That comes in handy around Holiday season).
Any idea of how to formulate the SQL or Delete Query? Currently there is the "Code" from the query...
DELETE DISTINCTROW [TEST D].[EFF-DTE], [TEST D].[CO-NBR], [TEST D].[CC-NBR], [TEST D].ROLL, [TEST D].[LOA-HRS], [TEST D].[VAC-HRS], [TEST D].[NCDE-HRS], [TEST D].[OTHR-HRS], [TEST D].[SCH-RG], [TEST D].[SCH-OT], [TEST D].[SCH-DT], [TEST D].[BOR-RG], [TEST D].[BOR-OT], [TEST D].[BOR-DT], [TEST D].[LOAN-RG], [TEST D].[LOAN-OT], [TEST D].[LOAN-DT], [TEST D].[WKO-RG], [TEST D].[WKO-OT], [TEST D].[WKO-DT], [TEST D].[CC-DSC]
FROM [TEST D]
WHERE ((([TEST D].[CC-NBR])<>9999));
As you can see I am using a "Test" table, however the field names are correct, as well as not deleting "9999".
Thank you for any and all help!
I'm assuming BuildWhere(Me, "OR") builds a WHERE string based on your current form.
You can't use the BuildWhere function in the query because (I'm assuming) it's filtering more than one column. Rather, in each field, you'd have to point it to the control of the form. For example, if I have a form frmTest with cboBox1 and cboBox2, and a table tblTest with Field1, Field2, Field3, ... and I want to delete all records where Field1 = cboBox1 and Field2 = cboBox2, then in the Criteria of Field1, I would put Forms!frmTest!cboBox1, and in the Criteria of Field2, I would put Forms!frmTest!cboBox2.
If you want to use your BuildWhere function, you would actually need to do that in VB. So instead of using the DoCmd.OpenQuery, you would use DoCmd.RunSQL "DELETE FROM Test WHERE " & BuildWhere(Me, "OR")
You can't use the BuildWhere function in the query because (I'm assuming) it's filtering more than one column. Rather, in each field, you'd have to point it to the control of the form. For example, if I have a form frmTest with cboBox1 and cboBox2, and a table tblTest with Field1, Field2, Field3, ... and I want to delete all records where Field1 = cboBox1 and Field2 = cboBox2, then in the Criteria of Field1, I would put Forms!frmTest!cboBox1, and in the Criteria of Field2, I would put Forms!frmTest!cboBox2.
If you want to use your BuildWhere function, you would actually need to do that in VB. So instead of using the DoCmd.OpenQuery, you would use DoCmd.RunSQL "DELETE FROM Test WHERE " & BuildWhere(Me, "OR")
- Thread starter
- #6
- Status
Similar threads
