473,326 Members | 2,090 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Query builder vs. vba for timestamp criteria

EJO
In the query builder, I can create a query that will take a date/time
as criteria, and the query finds the records:

SELECT SRSrvcsEquip.Stock, SRSrvcsEquip.Activity,
SRSrvcsEquip.EquipOwned, SRSrvcsEquip.Manufacturer, SRSrvcsEquip.EHSN,
SRSrvcsEquip.Qty, SRSrvcsEquip.PartNumber, SRSrvcsEquip.Description,
SRSrvcsEquip.ListType, SRSrvcsEquip.Site_ID, SRSrvcsEquip.SR_ID,
SRSrvcsEquip.ListDate, SRSrvcsEquip.ID
FROM SRSrvcsEquip
WHERE (((SRSrvcsEquip.ListDate)=#7/13/2006 10:25:0#));
Whereas this will not:

**************************************
Private Sub Command13_Click()

Dim strSql As String, dtListDate As String, intListType As Integer,
intSR_ID As Integer, intSite_ID As Integer, strCriteria As String

dtListDate = Now
intListType = Me.OpenArgs
intSR_ID = Me.SR_ID
intSite_ID = Me.Site_ID

strSql = "INSERT INTO SRSrvcsEquip ( Activity, EquipOwned,
Manufacturer, EHSN, Qty, PartNumber, Description, Stock, ListType,
Site_ID, SR_ID, ListDate) SELECT [SRSrvcsEquipDefaults].[Activity],
[SRSrvcsEquipDefaults].[EquipOwned],
[SRSrvcsEquipDefaults].[Manufacturer], [SRSrvcsEquipDefaults].[EHSN],
[SRSrvcsEquipDefaults].[Qty], [SRSrvcsEquipDefaults].[PartNumber],
[SRSrvcsEquipDefaults].[Description], [SRSrvcsEquipDefaults].[Stock], "
& intListType & ", " & intSite_ID & ", " & intSR_ID & " AS SR, #" &
dtListDate & "# FROM SRSrvcsEquipDefaults WHERE
((([SRSrvcsEquipDefaults].[ListName])=[Forms]![OLSelect]![Combo1]))
ORDER BY [SRSrvcsEquipDefaults].[ID];"

strCriteria = "[SR_ID] = " & intSR_ID & " AND[ListType] = " &
intListType & " AND[ListDate] = #" & dtListDate & "#"

With DoCmd
.SetWarnings 0
.RunSQL strSql
.SetWarnings -1
.Close
.OpenForm "OLEdit", , , strCriteria, , , intListType
End With

End Sub

*****************************************
Private Sub Form_Open(Cancel As Integer)

Dim strSql As String, strListName As String, intSite_ID As Integer

DoCmd.Maximize
If IsNumeric(Me.OpenArgs) = True Then
strSql = "Select * From SRSrvcsEquip;"
Me.ListDate.Visible = True
Me.ListName.Visible = False
Me.txtSC.Visible = True
Me.txtSR.Visible = True
Me.Command22.Visible = True
Else
strSql = "SELECT * FROM SRSrvcsEquipDefaults Where " & Me.OpenArgs
& ";"
Me.ListDate.Visible = False
Me.ListName.Visible = True
Me.txtSC.Visible = False
Me.txtSR.Visible = False
Me.Command22.Visible = False
End If

Me.RecordSource = strSql

End Sub

**************************************
Instead of the records with the specific datetime, I get a recordset
without regard to the criteria and can't build the list to allow for
version control.

Thanks,

EJO

Jul 13 '06 #1
4 4010
EJO wrote:
In the query builder, I can create a query that will take a date/time
as criteria, and the query finds the records:

SELECT SRSrvcsEquip.Stock, SRSrvcsEquip.Activity,
SRSrvcsEquip.EquipOwned, SRSrvcsEquip.Manufacturer, SRSrvcsEquip.EHSN,
SRSrvcsEquip.Qty, SRSrvcsEquip.PartNumber, SRSrvcsEquip.Description,
SRSrvcsEquip.ListType, SRSrvcsEquip.Site_ID, SRSrvcsEquip.SR_ID,
SRSrvcsEquip.ListDate, SRSrvcsEquip.ID
FROM SRSrvcsEquip
WHERE (((SRSrvcsEquip.ListDate)=#7/13/2006 10:25:0#));
Whereas this will not:

**************************************
Private Sub Command13_Click()

Dim strSql As String, dtListDate As String, intListType As Integer,
intSR_ID As Integer, intSite_ID As Integer, strCriteria As String

dtListDate = Now
intListType = Me.OpenArgs
intSR_ID = Me.SR_ID
intSite_ID = Me.Site_ID

strSql = "INSERT INTO SRSrvcsEquip ( Activity, EquipOwned,
Manufacturer, EHSN, Qty, PartNumber, Description, Stock, ListType,
Site_ID, SR_ID, ListDate) SELECT [SRSrvcsEquipDefaults].[Activity],
[SRSrvcsEquipDefaults].[EquipOwned],
[SRSrvcsEquipDefaults].[Manufacturer], [SRSrvcsEquipDefaults].[EHSN],
[SRSrvcsEquipDefaults].[Qty], [SRSrvcsEquipDefaults].[PartNumber],
[SRSrvcsEquipDefaults].[Description], [SRSrvcsEquipDefaults].[Stock], "
& intListType & ", " & intSite_ID & ", " & intSR_ID & " AS SR, #" &
dtListDate & "# FROM SRSrvcsEquipDefaults WHERE
((([SRSrvcsEquipDefaults].[ListName])=[Forms]![OLSelect]![Combo1]))
ORDER BY [SRSrvcsEquipDefaults].[ID];"

strCriteria = "[SR_ID] = " & intSR_ID & " AND[ListType] = " &
intListType & " AND[ListDate] = #" & dtListDate & "#"

With DoCmd
.SetWarnings 0
.RunSQL strSql
.SetWarnings -1
.Close
.OpenForm "OLEdit", , , strCriteria, , , intListType
End With

End Sub

*****************************************
Private Sub Form_Open(Cancel As Integer)

Dim strSql As String, strListName As String, intSite_ID As Integer

DoCmd.Maximize
If IsNumeric(Me.OpenArgs) = True Then
strSql = "Select * From SRSrvcsEquip;"
Me.ListDate.Visible = True
Me.ListName.Visible = False
Me.txtSC.Visible = True
Me.txtSR.Visible = True
Me.Command22.Visible = True
Else
strSql = "SELECT * FROM SRSrvcsEquipDefaults Where " & Me.OpenArgs
& ";"
Me.ListDate.Visible = False
Me.ListName.Visible = True
Me.txtSC.Visible = False
Me.txtSR.Visible = False
Me.Command22.Visible = False
End If

Me.RecordSource = strSql

End Sub

**************************************
Instead of the records with the specific datetime, I get a recordset
without regard to the criteria and can't build the list to allow for
version control.

Thanks,

EJO
Open the code window for the form. To the left of the Docmd.Maximize
press the mouse button on the bar. A circle will appear. Now close the
form and run. When it hits the Maximize, it will step you through the
code. Most likely OpenArgs is numeric and you don't get a filtered list.

Also, you are passing intListType and not the Where clause string.

I think if you do some debugging you'll find the flaws in your logic.

Hint: I sometimes will enter a line like
msgbox strSQL
and when I run the code see if the sql with the where clause is what I
expect. I recommend you step through your code and use a couple of
msgboxes.
Jul 13 '06 #2
EJO
Open the code window for the form. To the left of the Docmd.Maximize
press the mouse button on the bar. A circle will appear. Now close the
form and run. When it hits the Maximize, it will step you through the
code. Most likely OpenArgs is numeric and you don't get a filtered list.

Also, you are passing intListType and not the Where clause string.

I think if you do some debugging you'll find the flaws in your logic.

Hint: I sometimes will enter a line like
msgbox strSQL
and when I run the code see if the sql with the where clause is what I
expect. I recommend you step through your code and use a couple of
msgboxes.
Thanks, Salad; i've debugged it time and again (even with the msgbox to
check the criteria, which is being passed via DoCmd.OpenForm).
Everything but the recordset result is coming through as I expect.
When I take out the date/time portion, the results are as expected
given the remaining criteria. Mostly, I was hoping it was just
something I was overlooking.

I've even gone so far as to turn the criteria to have the date/time in
a range using dateadd(), -1 sec on the front and add 1 on the back:

strCriteria = "[SR_ID] = " & intSR_ID & " AND[ListType] = " &
intListType & " AND[ListDate] Between #" & DateAdd("s", -1,
dtListDate) & "# AND #" & DateAdd("s", 1, dtListDate) & "#"

Jul 14 '06 #3
EJO wrote:
>>Open the code window for the form. To the left of the Docmd.Maximize
press the mouse button on the bar. A circle will appear. Now close the
form and run. When it hits the Maximize, it will step you through the
code. Most likely OpenArgs is numeric and you don't get a filtered list.

Also, you are passing intListType and not the Where clause string.

I think if you do some debugging you'll find the flaws in your logic.

Hint: I sometimes will enter a line like
msgbox strSQL
and when I run the code see if the sql with the where clause is what I
expect. I recommend you step through your code and use a couple of
msgboxes.


Thanks, Salad; i've debugged it time and again (even with the msgbox to
check the criteria, which is being passed via DoCmd.OpenForm).
Everything but the recordset result is coming through as I expect.
When I take out the date/time portion, the results are as expected
given the remaining criteria. Mostly, I was hoping it was just
something I was overlooking.

I've even gone so far as to turn the criteria to have the date/time in
a range using dateadd(), -1 sec on the front and add 1 on the back:

strCriteria = "[SR_ID] = " & intSR_ID & " AND[ListType] = " &
intListType & " AND[ListDate] Between #" & DateAdd("s", -1,
dtListDate) & "# AND #" & DateAdd("s", 1, dtListDate) & "#"
OK. Your SQL statement was large enough I didn't want to get involved
with it. However, I decided to drop it into a module and I got the
typical red line incorrect syntax error.

strSql = "INSERT INTO SRSrvcsEquip ( Activity, EquipOwned,
Manufacturer, EHSN, Qty, PartNumber, Description, Stock, ListType,
Site_ID, SR_ID, ListDate) SELECT [SRSrvcsEquipDefaults].[Activity],
[SRSrvcsEquipDefaults].[EquipOwned],
[SRSrvcsEquipDefaults].[Manufacturer], [SRSrvcsEquipDefaults].[EHSN],
[SRSrvcsEquipDefaults].[Qty], [SRSrvcsEquipDefaults].[PartNumber],
[SRSrvcsEquipDefaults].[Description], [SRSrvcsEquipDefaults].[Stock], "
& intListType & ", " & intSite_ID & ", " & intSR_ID & " AS SR, #" &
dtListDate & "# FROM SRSrvcsEquipDefaults WHERE
((([SRSrvcsEquipDefaults].[ListName])=[Forms]![OLSelect]![Combo1]))
ORDER BY [SRSrvcsEquipDefaults].[ID];"

Now, I decided that there were no underscore _'s so this must be
wordwrapped. So I put it onto 1 line. And it was still red-lined.

I noticed the quotes in the strSQL assignment and so I fixed it with dbl
quotes.

Since this statement, when executed, will add records, and you have
warnings off, maybe no records are being added and for whatever reason
you aren't getting an error.

Could you fix your statement with proper quote markings and then run
your code. It's a frigging PITA that you can't take a SQL statement
generated from the Query Builder and plop it into a variable. I hate
all the quotes that you sometimes need to add, chopping lines into parts
using the underscore, etc as you build a dynamic SQL statement. It's
too bad they didn't have an add-in tool to drop in a SQL statement and
have it correctly create the
strSQL = "...
statement that could be plugged into code. I can't imagine how many
1000's, maybe millions of manhours that have been spent by developers
worldwide fixing the statement that comes from the builder.

Now, I'm not saying that's the source of your error. But if no records
get added you certainly aren't going to find any.
Jul 14 '06 #4
* EJO:
>Open the code window for the form. To the left of the Docmd.Maximize
press the mouse button on the bar. A circle will appear. Now close the
form and run. When it hits the Maximize, it will step you through the
code. Most likely OpenArgs is numeric and you don't get a filtered list.

Also, you are passing intListType and not the Where clause string.

I think if you do some debugging you'll find the flaws in your logic.

Hint: I sometimes will enter a line like
msgbox strSQL
and when I run the code see if the sql with the where clause is what I
expect. I recommend you step through your code and use a couple of
msgboxes.

Thanks, Salad; i've debugged it time and again (even with the msgbox to
check the criteria, which is being passed via DoCmd.OpenForm).
Everything but the recordset result is coming through as I expect.
When I take out the date/time portion, the results are as expected
given the remaining criteria. Mostly, I was hoping it was just
something I was overlooking.

I've even gone so far as to turn the criteria to have the date/time in
a range using dateadd(), -1 sec on the front and add 1 on the back:

strCriteria = "[SR_ID] = " & intSR_ID & " AND[ListType] = " &
intListType & " AND[ListDate] Between #" & DateAdd("s", -1,
dtListDate) & "# AND #" & DateAdd("s", 1, dtListDate) & "#"
Pardon my butting in here, but I think I see the problem. By resetting
the RecordSource in the Form_Open, you are clearing the form criteria.

Perhaps, you could simply add a filter in the Form_Open to get the
desired result? Or, perhaps you could add the criteria to the Insert
SQL that you are running in the previous form.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Jul 14 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: John young | last post by:
I have been looking for an answer to a problem and have found this group and hope you can assist . I have been re doing a data base I have made for a car club I am with and have been trying to...
2
by: NigelMThomas | last post by:
I have an especially challenging problem. I know there are a few geniuses in this group so perhaps; you can advise me whether or not this can be done as an update query in Access. Thanks. I am...
3
by: Don Sealer | last post by:
I'm guessing this is pretty simple however not simple enough for me. I'm developing a database to track expenses, income, banking transactions, etc. I have a very simple query with four fields,...
0
by: sales | last post by:
I am glad to present to the community of this group the new version of our component intended for visual building of SQL queries via an intuitive interface - Active Query Builder...
33
by: Birky | last post by:
Can you please help me understand how to build Criteria syntax within the Query Builder? If you have a field that is for a region (lets say for Midwest) and you have another field which breaks...
22
by: Stan | last post by:
I am working with Access 2003 on a computer running XP. I am new at using Access. I have a Db with a date field stored as mm/dd/yyyy. I need a Query that will prompt for the month, ie. 6 for...
3
by: Thorben Grosser | last post by:
Hello Newsgroup, I am doing some archive database and therefore got one table indexing every folder and one table storing which rack belongs to which department, eg: table folders :...
2
by: Coll | last post by:
I have a form and a query. I would like to have some control on the form (check box probably), that when selected will limit the criteria for a field in a query. Here are the details.... When...
2
by: iheartvba | last post by:
Hi, I usually use access query builder to create complex queries. Especially to create Sub Queries I.E. a querying another query. In my current situation I need to provide the criteria to the query...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.