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.St ock, SRSrvcsEquip.Ac tivity,
SRSrvcsEquip.Eq uipOwned, SRSrvcsEquip.Ma nufacturer, SRSrvcsEquip.EH SN,
SRSrvcsEquip.Qt y, SRSrvcsEquip.Pa rtNumber, SRSrvcsEquip.De scription,
SRSrvcsEquip.Li stType, SRSrvcsEquip.Si te_ID, SRSrvcsEquip.SR _ID,
SRSrvcsEquip.Li stDate, 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 [SRSrvcsEquipDef aults].[Activity],
[SRSrvcsEquipDef aults].[EquipOwned],
[SRSrvcsEquipDef aults].[Manufacturer], [SRSrvcsEquipDef aults].[EHSN],
[SRSrvcsEquipDef aults].[Qty], [SRSrvcsEquipDef aults].[PartNumber],
[SRSrvcsEquipDef aults].[Description], [SRSrvcsEquipDef aults].[Stock], "
& intListType & ", " & intSite_ID & ", " & intSR_ID & " AS SR, #" &
dtListDate & "# FROM SRSrvcsEquipDef aults WHERE
((([SRSrvcsEquipDef aults].[ListName])=[Forms]![OLSelect]![Combo1]))
ORDER BY [SRSrvcsEquipDef aults].[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(Cance l As Integer)

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

DoCmd.Maximize
If IsNumeric(Me.Op enArgs) = True Then
strSql = "Select * From SRSrvcsEquip;"
Me.ListDate.Vis ible = True
Me.ListName.Vis ible = False
Me.txtSC.Visibl e = True
Me.txtSR.Visibl e = True
Me.Command22.Vi sible = True
Else
strSql = "SELECT * FROM SRSrvcsEquipDef aults Where " & Me.OpenArgs
& ";"
Me.ListDate.Vis ible = False
Me.ListName.Vis ible = True
Me.txtSC.Visibl e = False
Me.txtSR.Visibl e = False
Me.Command22.Vi sible = 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

[salad]

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.St ock, SRSrvcsEquip.Ac tivity,
SRSrvcsEquip.Eq uipOwned, SRSrvcsEquip.Ma nufacturer, SRSrvcsEquip.EH SN,
SRSrvcsEquip.Qt y, SRSrvcsEquip.Pa rtNumber, SRSrvcsEquip.De scription,
SRSrvcsEquip.Li stType, SRSrvcsEquip.Si te_ID, SRSrvcsEquip.SR _ID,
SRSrvcsEquip.Li stDate, 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 [SRSrvcsEquipDef aults].[Activity],
[SRSrvcsEquipDef aults].[EquipOwned],
[SRSrvcsEquipDef aults].[Manufacturer], [SRSrvcsEquipDef aults].[EHSN],
[SRSrvcsEquipDef aults].[Qty], [SRSrvcsEquipDef aults].[PartNumber],
[SRSrvcsEquipDef aults].[Description], [SRSrvcsEquipDef aults].[Stock], "
& intListType & ", " & intSite_ID & ", " & intSR_ID & " AS SR, #" &
dtListDate & "# FROM SRSrvcsEquipDef aults WHERE
((([SRSrvcsEquipDef aults].[ListName])=[Forms]![OLSelect]![Combo1]))
ORDER BY [SRSrvcsEquipDef aults].[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(Cance l As Integer)

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

DoCmd.Maximize
If IsNumeric(Me.Op enArgs) = True Then
strSql = "Select * From SRSrvcsEquip;"
Me.ListDate.Vis ible = True
Me.ListName.Vis ible = False
Me.txtSC.Visibl e = True
Me.txtSR.Visibl e = True
Me.Command22.Vi sible = True
Else
strSql = "SELECT * FROM SRSrvcsEquipDef aults Where " & Me.OpenArgs
& ";"
Me.ListDate.Vis ible = False
Me.ListName.Vis ible = True
Me.txtSC.Visibl e = False
Me.txtSR.Visibl e = False
Me.Command22.Vi sible = 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.

[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) & "#"

[salad]

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 [SRSrvcsEquipDef aults].[Activity],
[SRSrvcsEquipDef aults].[EquipOwned],
[SRSrvcsEquipDef aults].[Manufacturer], [SRSrvcsEquipDef aults].[EHSN],
[SRSrvcsEquipDef aults].[Qty], [SRSrvcsEquipDef aults].[PartNumber],
[SRSrvcsEquipDef aults].[Description], [SRSrvcsEquipDef aults].[Stock], "
& intListType & ", " & intSite_ID & ", " & intSR_ID & " AS SR, #" &
dtListDate & "# FROM SRSrvcsEquipDef aults WHERE
((([SRSrvcsEquipDef aults].[ListName])=[Forms]![OLSelect]![Combo1]))
ORDER BY [SRSrvcsEquipDef aults].[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.

[Randy Harris]

* 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.