Dynamic QBF - concatenating - HELP!

  • Thread starter Thread starter MaryAnn
  • Start date Start date
M

MaryAnn

thanks for reading - here's my code. New problem here is
that anytime I use the date range, it errors. It also is
adding parameters together. Any help is appreciated.
Again, thank you so much!

(e-mail address removed)


Option Compare Database
Option Explicit

Private Sub cmdRunQuery_Click()

Dim MyDatabase As Database
Dim MyQueryDef As QueryDef
Dim rs As Recordset
Dim where As Variant

Set MyDatabase = CurrentDb()

If ObjectExists("Queries", "qryDynamic_QBF") = True
Then
MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
MyDatabase.QueryDefs.Refresh

End If

where = Null
where = where & " AND [Clock Number]= '" + Me!
[txtClock]
where = where & " AND [Last Name]= '" + Me!
[Text15]
where = where & " AND [Date of Hire] Between " &
Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " & Format
(Me![txtEnd], "\#m\/d\/yyyy\#")
where = where & " AND [Location]= '" + Me![Text12]
where = where & " AND [Title]= '" + Me![Combo23]
where = where & " AND [Union]= '" + Me![Combo27]

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))

Set Recordset = MyDatabase.OpenRecordset("Select *
from EADDataForPhotos " & (" where " + Mid(where, 6)
& ";"))

If Recordset.RecordCount = 0 Then
MsgBox "No Records were found"
Else
DoCmd.OpenQuery "qryDynamic_QBF"
DoCmd.OpenForm "Search Results"
Forms![Search Results].Requery
DoCmd.Close acQuery, "qryDynamic_QBF"


End If

End Sub
 
Not familiar with your syntax for the date range section,
but adding a closing single quote should correct the
adding parameters. Example below.
....
where = where & " AND [Location]= '" & Me![Text12] & "'"
where = where & " AND [Title]= '" & Me![Combo23] & "'"
where = where & " AND [Union]= '" & Me![Combo27] & "'"
....

You could also comment out all the recordset calls and use
a debug.print "SELECT ... etc" to examine the rest of the
SQL statment in the immediate window.

debug.print "Select * from EADDataForPhotos where " & Mid
(where, 6) & ";"

'comment out the rest of your routing starting here
'Set MyQueryDef = etc...
 
I had it that way - with the closing single quotes, but it
still comes up with the following:

Runtime Error 3075

Syntax error in string in query expression '[ClockNumber]
='12054' AND [Date of Hire] Between And; ',

Any further ideas? I am just getting started and am
really a little lost.

thank you so much.
-----Original Message-----
Not familiar with your syntax for the date range section,
but adding a closing single quote should correct the
adding parameters. Example below.
....
where = where & " AND [Location]= '" & Me![Text12] & "'"
where = where & " AND [Title]= '" & Me![Combo23] & "'"
where = where & " AND [Union]= '" & Me![Combo27] & "'"
....

You could also comment out all the recordset calls and use
a debug.print "SELECT ... etc" to examine the rest of the
SQL statment in the immediate window.

debug.print "Select * from EADDataForPhotos where " & Mid
(where, 6) & ";"

'comment out the rest of your routing starting here
'Set MyQueryDef = etc...

-----Original Message-----
thanks for reading - here's my code. New problem here is
that anytime I use the date range, it errors. It also is
adding parameters together. Any help is appreciated.
Again, thank you so much!

(e-mail address removed)


Option Compare Database
Option Explicit

Private Sub cmdRunQuery_Click()

Dim MyDatabase As Database
Dim MyQueryDef As QueryDef
Dim rs As Recordset
Dim where As Variant

Set MyDatabase = CurrentDb()

If ObjectExists("Queries", "qryDynamic_QBF") = True
Then
MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
MyDatabase.QueryDefs.Refresh

End If

where = Null
where = where & " AND [Clock Number]= '" + Me!
[txtClock]
where = where & " AND [Last Name]= '" + Me!
[Text15]
where = where & " AND [Date of Hire] Between " &
Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " & Format
(Me![txtEnd], "\#m\/d\/yyyy\#")
where = where & " AND [Location]= '" + Me![Text12]
where = where & " AND [Title]= '" + Me![Combo23]
where = where & " AND [Union]= '" + Me![Combo27]

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))

Set Recordset = MyDatabase.OpenRecordset("Select *
from EADDataForPhotos " & (" where " + Mid(where, 6)
& ";"))

If Recordset.RecordCount = 0 Then
MsgBox "No Records were found"
Else
DoCmd.OpenQuery "qryDynamic_QBF"
DoCmd.OpenForm "Search Results"
Forms![Search Results].Requery
DoCmd.Close acQuery, "qryDynamic_QBF"


End If

End Sub
.
Click to expand...
.
Click to expand...
 
MaryAnn, you were supposed to replace txtStart and txtEnd
with your own names for the text boxes with the range's
start and end dates.

I don't unserstand what you mean by "adding parameters
together". Could you post an example?
 
Did you try to view the entire SQL statement in the
immediate window? It would be nice if we could see
exactly what is going on.
If you don't know how do comment out a section of code,
go to the view->toolbars menu in the visual basic editor
and make sure the 'Edit' tool bar is checked.
Select a few lines of your code and hit the 'commentblock'
icon. The code should turn green and will be skipped.
You can do this starting at the section mentioned
previously up to but not including 'End Sub'.
When your sub routine runs, instead of producing an error,
it will print out your SQL statement to the immediate
window.

-----Original Message-----
I had it that way - with the closing single quotes, but it
still comes up with the following:

Runtime Error 3075

Syntax error in string in query expression '[ClockNumber]
='12054' AND [Date of Hire] Between And; ',

Any further ideas? I am just getting started and am
really a little lost.

thank you so much.
-----Original Message-----
Not familiar with your syntax for the date range section,
but adding a closing single quote should correct the
adding parameters. Example below.
....
where = where & " AND [Location]= '" & Me![Text12] & "'"
where = where & " AND [Title]= '" & Me![Combo23] & "'"
where = where & " AND [Union]= '" & Me![Combo27] & "'"
....

You could also comment out all the recordset calls and use
a debug.print "SELECT ... etc" to examine the rest of the
SQL statment in the immediate window.

debug.print "Select * from EADDataForPhotos where " & Mid
(where, 6) & ";"

'comment out the rest of your routine starting here
'Set MyQueryDef = etc...

-----Original Message-----
thanks for reading - here's my code. New problem here is
that anytime I use the date range, it errors. It also is
adding parameters together. Any help is appreciated.
Again, thank you so much!

(e-mail address removed)


Option Compare Database
Option Explicit

Private Sub cmdRunQuery_Click()

Dim MyDatabase As Database
Dim MyQueryDef As QueryDef
Dim rs As Recordset
Dim where As Variant

Set MyDatabase = CurrentDb()

If ObjectExists("Queries", "qryDynamic_QBF") = True
Then
MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
MyDatabase.QueryDefs.Refresh

End If

where = Null
where = where & " AND [Clock Number]= '" + Me!
[txtClock]
where = where & " AND [Last Name]= '" + Me!
[Text15]
where = where & " AND [Date of Hire] Between " &
Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " & Format
(Me![txtEnd], "\#m\/d\/yyyy\#")
where = where & " AND [Location]= '" + Me![Text12]
where = where & " AND [Title]= '" + Me![Combo23]
where = where & " AND [Union]= '" + Me![Combo27]

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))

Set Recordset = MyDatabase.OpenRecordset("Select *
from EADDataForPhotos " & (" where " + Mid(where, 6)
& ";"))

If Recordset.RecordCount = 0 Then
MsgBox "No Records were found"
Else
DoCmd.OpenQuery "qryDynamic_QBF"
DoCmd.OpenForm "Search Results"
Forms![Search Results].Requery
DoCmd.Close acQuery, "qryDynamic_QBF"


End If

End Sub
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Marsh,

I named the text boxes txtStart and txtEnd. The error I
am getting is as follows (in this example I put a name in
the Last Name field:

Run-time error '3075':

Syntax error (missing operator) in query
expression '[LastName]='Smith' AND [Date of Hire] Between
And ';'.

The code that is then highlighted (when I hit 'debug') is:

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))


Also, here's the complete code I have....

Option Compare Database
Option Explicit

Private Sub cmdRunQuery_Click()

Dim MyDatabase As Database
Dim MyQueryDef As QueryDef
Dim rs As Recordset
Dim where As Variant

Set MyDatabase = CurrentDb()

If ObjectExists("Queries", "qryDynamic_QBF") = True
Then
MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
MyDatabase.QueryDefs.Refresh

End If

where = Null
where = where & " AND [Clock Number]= '" + Me!
[txtClock] + "'"
where = where & " AND [Last Name]= '" + Me!
[Text15] + "'"
where = where & " AND [Date of Hire] Between " &
Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " & Format
(Me![txtEnd], "\#m\/d\/yyyy\#") + "'"
where = where & " AND [Location]= '" + Me![Text12]
+ "'"
where = where & " AND [Title]= '" + Me![Combo23]
+ "'"
where = where & " AND [Union]= '" + Me![Combo27]
+ "'"

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))

Set Recordset = MyDatabase.OpenRecordset("Select *
from EADDataForPhotos " & (" where " + Mid(where, 6)
& ";"))

If Recordset.RecordCount = 0 Then
MsgBox "No Records were found"
Else
DoCmd.OpenQuery "qryDynamic_QBF"
DoCmd.OpenForm "Search Results"
Forms![Search Results].Requery
DoCmd.Close acQuery, "qryDynamic_QBF"


End If

End Sub

-----Original Message-----
MaryAnn, you were supposed to replace txtStart and txtEnd
with your own names for the text boxes with the range's
start and end dates.

I don't unserstand what you mean by "adding parameters
together". Could you post an example?
--
Marsh
MVP [MS Access]


thanks for reading - here's my code. New problem here is
that anytime I use the date range, it errors. It also is
adding parameters together. Any help is appreciated.
Again, thank you so much!

(e-mail address removed)


Option Compare Database
Option Explicit

Private Sub cmdRunQuery_Click()

Dim MyDatabase As Database
Dim MyQueryDef As QueryDef
Dim rs As Recordset
Dim where As Variant

Set MyDatabase = CurrentDb()

If ObjectExists("Queries", "qryDynamic_QBF") = True
Then
MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
MyDatabase.QueryDefs.Refresh

End If

where = Null
where = where & " AND [Clock Number]= '" + Me!
[txtClock]
where = where & " AND [Last Name]= '" + Me!
[Text15]
where = where & " AND [Date of Hire] Between " &
Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " & Format
(Me![txtEnd], "\#m\/d\/yyyy\#")
where = where & " AND [Location]= '" + Me![Text12]
where = where & " AND [Title]= '" + Me![Combo23]
where = where & " AND [Union]= '" + Me![Combo27]

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))

Set Recordset = MyDatabase.OpenRecordset("Select *
from EADDataForPhotos " & (" where " + Mid(where, 6)
& ";"))

If Recordset.RecordCount = 0 Then
MsgBox "No Records were found"
Else
DoCmd.OpenQuery "qryDynamic_QBF"
DoCmd.OpenForm "Search Results"
Forms![Search Results].Requery
DoCmd.Close acQuery, "qryDynamic_QBF"


End If

End Sub
Click to expand...

.
Click to expand...
 
Marsh,

I named the text boxes txtStart and txtEnd. The error I
am getting is as follows (in this example I put a name in
the Last Name field:

Run-time error '3075':

Syntax error (missing operator) in query
expression '[LastName]='Smith' AND [Date of Hire] Between
And ';'.

The code that is then highlighted (when I hit 'debug') is:

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))


Also, here's the complete code I have.... [snip]

where = Null
where = where & " AND [Clock Number]= '" + Me!
[txtClock] + "'"
where = where & " AND [Last Name]= '" + Me!
[Text15] + "'"
where = where & " AND [Date of Hire] Between " &
Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " & Format
(Me![txtEnd], "\#m\/d\/yyyy\#") + "'"
where = where & " AND [Location]= '" + Me![Text12]
+ "'"
where = where & " AND [Title]= '" + Me![Combo23]
+ "'"
where = where & " AND [Union]= '" + Me![Combo27]
+ "'"
Click to expand...
[snip]


Man, am I dense. I completely missed your use of + to
sppress conditions that were not specified. Unfortunately,
that will not work with Format since it always returns a
string. Use an If statement around the date range part to
suppress the condition when nothing is specified in txtStart
or txtEnd.

If Not ( IsNull(txtStart) Or IsNull(txtEnd) ) Then
where = where & " AND [Date of Hire] Between " _
& Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " _
& Format(Me![txtEnd], "\#m\/d\/yyyy\#") & "'"
End If
 
Marsh,

Still getting runtime error 3075. syntax error in the
[Date of Hire] statement. HELP! And, you? Dense? I hope
I grow to be dense too then. So in over my head right now!

Thank you so much for all your continued help. I really
really appreciate it!

Mary Ann




-----Original Message-----
Marsh,

I named the text boxes txtStart and txtEnd. The error I
am getting is as follows (in this example I put a name in
the Last Name field:

Run-time error '3075':

Syntax error (missing operator) in query
expression '[LastName]='Smith' AND [Date of Hire] Between
And ';'.

The code that is then highlighted (when I hit 'debug') is:

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))


Also, here's the complete code I have.... [snip]

where = Null
where = where & " AND [Clock Number]= '" + Me!
[txtClock] + "'"
where = where & " AND [Last Name]= '" + Me!
[Text15] + "'"
where = where & " AND [Date of Hire] Between " &
Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " & Format
(Me![txtEnd], "\#m\/d\/yyyy\#") + "'"
where = where & " AND [Location]= '" + Me![Text12]
+ "'"
where = where & " AND [Title]= '" + Me![Combo23]
+ "'"
where = where & " AND [Union]= '" + Me![Combo27]
+ "'"
Click to expand...
[snip]


Man, am I dense. I completely missed your use of + to
sppress conditions that were not specified. Unfortunately,
that will not work with Format since it always returns a
string. Use an If statement around the date range part to
suppress the condition when nothing is specified in txtStart
or txtEnd.

If Not ( IsNull(txtStart) Or IsNull(txtEnd) ) Then
where = where & " AND [Date of Hire] Between " _
& Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " _
& Format(Me![txtEnd], "\#m\/d\/yyyy\#") & "'"
End If
Click to expand...
 
MaryAnn said:
Marsh,

Still getting runtime error 3075. syntax error in the
[Date of Hire] statement. HELP! And, you? Dense? I hope
I grow to be dense too then. So in over my head right now!

Thank you so much for all your continued help. I really
really appreciate it!
Click to expand...

One missed item after another, what a day.

Remove the + "'" at the end of that statement.

If that doesn't fix it, post back with a Copy/Paste of that
line of code and the result shown in the message box.
--
Marsh
MVP [MS Access]



-----Original Message-----
Marsh,

I named the text boxes txtStart and txtEnd. The error I
am getting is as follows (in this example I put a name in
the Last Name field:

Run-time error '3075':

Syntax error (missing operator) in query
expression '[LastName]='Smith' AND [Date of Hire] Between
And ';'.

The code that is then highlighted (when I hit 'debug') is:

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))


Also, here's the complete code I have.... [snip]

where = Null
where = where & " AND [Clock Number]= '" + Me!
[txtClock] + "'"
where = where & " AND [Last Name]= '" + Me!
[Text15] + "'"
where = where & " AND [Date of Hire] Between " &
Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " & Format
(Me![txtEnd], "\#m\/d\/yyyy\#") + "'"
where = where & " AND [Location]= '" + Me![Text12]
+ "'"
where = where & " AND [Title]= '" + Me![Combo23]
+ "'"
where = where & " AND [Union]= '" + Me![Combo27]
+ "'"
Click to expand...
[snip]


Man, am I dense. I completely missed your use of + to
sppress conditions that were not specified. Unfortunately,
that will not work with Format since it always returns a
string. Use an If statement around the date range part to
suppress the condition when nothing is specified in txtStart
or txtEnd.

If Not ( IsNull(txtStart) Or IsNull(txtEnd) ) Then
where = where & " AND [Date of Hire] Between " _
& Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " _
& Format(Me![txtEnd], "\#m\/d\/yyyy\#") & "'"
End If
Click to expand...
Click to expand...
 
Since I couldn't get you to print the SQL, I did it myself
and think i found the error that will fix this.
Here's my suggestion.
Your date range ( between .. and ... criteria in the where
clause) should be seperated from the other "AND"'s. It's
not exactly the same. You do this by enclosing in
paranthesis.

where = Null
where = where & " AND [Clock Number]='" + Me![txtClock]
+ "'"
where = where & " AND [Last Name]='" + Me![text15] + "'"

'.... changed this part .........
where = where & " AND (([Date of Hire]) Between " & _
Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " & _
Format(Me![txtEnd], "\#m\/d\/yyyy\#") + ")"
'.... end changed ...............

where = where & " AND [Location]= '" + Me![text12] + "'"
where = where & " AND [Title]= '" + Me![combo23] + "'"
where = where & " AND [Union]= '" + Me![combo27] + "'"


I'm also sending the code that I put together (based on
your code) to find it.
To run this, create a new module and paste in the sub
below.
Then open the immediate window and type in:
printWhereClauseThenSQL
and hit enter.

===========================================
Code:

Public Sub printWhereClauseThenSQL()

Dim where As Variant

'Set MyDatabase = CurrentDb()
'
'If ObjectExists("Queries", "qryDynamic_QBF") = True Then
'MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
'MyDatabase.QueryDefs.Refresh
'
'End If

Dim txtClock As String
Dim text15 As String
Dim txtStart As String
Dim txtEnd As String
Dim text12 As String
Dim combo23 As String
Dim combo27 As String

txtClock = "123478"
text15 = "text15"
txtStart = "1/4/2004"
txtEnd = "1/5/2004"
text12 = "text12"
combo23 = "combo23"
combo27 = "combo27"

where = Null
where = where & " AND [Clock Number]='" + txtClock + "'"
where = where & " AND [Last Name]='" + text15 + "'"
where = where & " AND (([Date of Hire]) Between " _
& Format(txtStart, "\#m\/d\/yyyy\#") & " And " _
& Format(txtEnd, "\#m\/d\/yyyy\#") + ")"
where = where & " AND [Location]= '" + text12 + "'"
where = where & " AND [Title]= '" + combo23 + "'"
where = where & " AND [Union]= '" + combo27 + "'"

'you can print the SQL and debug it

Debug.Print where
Debug.Print "Select * from EADDataForPhotos" & (" WHERE "
+ Mid(where, 6) & ";")

'Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
'"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))
'
'Set Recordset = MyDatabase.OpenRecordset("Select * from
EADDataForPhotos " & (" where " + Mid(where, 6) & ";"))
'
'If Recordset.RecordCount = 0 Then
'MsgBox "No Records were found"
'Else
'DoCmd.OpenQuery "qryDynamic_QBF"
'DoCmd.OpenForm "Search Results"
'Forms![Search Results].Requery
'DoCmd.Close acQuery, "qryDynamic_QBF"


'End If
 
Marsh,

It worked! Thank you so much! Only new problem. I was
playing with my switchboard and now it the switchboard
won't go away and i can't navigate to anything. I can't
bring up the database window or anything. what do I do so
that I can reset the settings on the switchboard?



-----Original Message-----
MaryAnn said:
Marsh,

Still getting runtime error 3075. syntax error in the
[Date of Hire] statement. HELP! And, you? Dense? I hope
I grow to be dense too then. So in over my head right now!

Thank you so much for all your continued help. I really
really appreciate it!
Click to expand...

One missed item after another, what a day.

Remove the + "'" at the end of that statement.

If that doesn't fix it, post back with a Copy/Paste of that
line of code and the result shown in the message box.
--
Marsh
MVP [MS Access]



-----Original Message-----

Marsh,

I named the text boxes txtStart and txtEnd. The error I
am getting is as follows (in this example I put a name in
the Last Name field:

Run-time error '3075':

Syntax error (missing operator) in query
expression '[LastName]='Smith' AND [Date of Hire] Between
And ';'.

The code that is then highlighted (when I hit 'debug') is:

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))


Also, here's the complete code I have....
[snip]

where = Null
where = where & " AND [Clock Number]= '" + Me!
[txtClock] + "'"
where = where & " AND [Last Name]= '" + Me!
[Text15] + "'"
where = where & " AND [Date of Hire] Between " &
Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " & Format
(Me![txtEnd], "\#m\/d\/yyyy\#") + "'"
where = where & " AND [Location]= '" + Me! [Text12]
+ "'"
where = where & " AND [Title]= '" + Me![Combo23]
+ "'"
where = where & " AND [Union]= '" + Me![Combo27]
+ "'"
[snip]


Man, am I dense. I completely missed your use of + to
sppress conditions that were not specified. Unfortunately,
that will not work with Format since it always returns a
string. Use an If statement around the date range part to
suppress the condition when nothing is specified in txtStart
or txtEnd.

If Not ( IsNull(txtStart) Or IsNull(txtEnd) ) Then
where = where & " AND [Date of Hire] Between " _
& Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " _
& Format(Me![txtEnd], "\#m\/d\/yyyy\#") & "'"
End If
Click to expand...
Click to expand...

.
Click to expand...
 
It worked! Thank you so much! Only new problem. I was
playing with my switchboard and now it the switchboard
won't go away and i can't navigate to anything. I can't
bring up the database window or anything. what do I do so
that I can reset the settings on the switchboard?
Click to expand...

I may not be the best person to answer this one. In
general, you should start a new thread for a new topic.

All I can think of to try is to hold down the Shift key when
you open the database.
--
Marsh
MVP [MS Access]



-----Original Message-----
MaryAnn said:
Marsh,

Still getting runtime error 3075. syntax error in the
[Date of Hire] statement. HELP! And, you? Dense? I hope
I grow to be dense too then. So in over my head right now!

Thank you so much for all your continued help. I really
really appreciate it!
Click to expand...

One missed item after another, what a day.

Remove the + "'" at the end of that statement.

If that doesn't fix it, post back with a Copy/Paste of that
line of code and the result shown in the message box.
--
Marsh
MVP [MS Access]



-----Original Message-----

Marsh,

I named the text boxes txtStart and txtEnd. The error I
am getting is as follows (in this example I put a name
in
the Last Name field:

Run-time error '3075':

Syntax error (missing operator) in query
expression '[LastName]='Smith' AND [Date of Hire]
Between
And ';'.

The code that is then highlighted (when I hit 'debug')
is:

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))


Also, here's the complete code I have....
[snip]

where = Null
where = where & " AND [Clock Number]= '" + Me!
[txtClock] + "'"
where = where & " AND [Last Name]= '" + Me!
[Text15] + "'"
where = where & " AND [Date of Hire] Between " &
Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " &
Format
(Me![txtEnd], "\#m\/d\/yyyy\#") + "'"
where = where & " AND [Location]= '" + Me! [Text12]
+ "'"
where = where & " AND [Title]= '" + Me![Combo23]
+ "'"
where = where & " AND [Union]= '" + Me![Combo27]
+ "'"
[snip]


Man, am I dense. I completely missed your use of + to
sppress conditions that were not specified.
Unfortunately,
that will not work with Format since it always returns a
string. Use an If statement around the date range part to
suppress the condition when nothing is specified in
txtStart
or txtEnd.

If Not ( IsNull(txtStart) Or IsNull(txtEnd) ) Then
where = where & " AND [Date of Hire] Between " _
& Format(Me![txtStart], "\#m\/d\/yyyy\#")
& " And " _
& Format(Me![txtEnd], "\#m\/d\/yyyy\#")
& "'"
End If
Click to expand...

.
Click to expand...
Click to expand...
 
Thanks again! I've gotten all the navigation now done and
I could not have done it without your help!

I really really appreciate your help.

Have a great day!

MAB
-----Original Message-----
It worked! Thank you so much! Only new problem. I was
playing with my switchboard and now it the switchboard
won't go away and i can't navigate to anything. I can't
bring up the database window or anything. what do I do so
that I can reset the settings on the switchboard?
Click to expand...

I may not be the best person to answer this one. In
general, you should start a new thread for a new topic.

All I can think of to try is to hold down the Shift key when
you open the database.
--
Marsh
MVP [MS Access]



-----Original Message-----
MaryAnn wrote:

Marsh,

Still getting runtime error 3075. syntax error in the
[Date of Hire] statement. HELP! And, you? Dense? I hope
I grow to be dense too then. So in over my head right now!

Thank you so much for all your continued help. I really
really appreciate it!

One missed item after another, what a day.

Remove the + "'" at the end of that statement.

If that doesn't fix it, post back with a Copy/Paste of that
line of code and the result shown in the message box.
--
Marsh
MVP [MS Access]




-----Original Message-----

Marsh,

I named the text boxes txtStart and txtEnd. The
Click to expand...
error
I
am getting is as follows (in this example I put a name
in
the Last Name field:

Run-time error '3075':

Syntax error (missing operator) in query
expression '[LastName]='Smith' AND [Date of Hire]
Between
And ';'.

The code that is then highlighted (when I hit 'debug')
is:

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))


Also, here's the complete code I have....
[snip]

where = Null
where = where & " AND [Clock Number]= '" + Me!
[txtClock] + "'"
where = where & " AND [Last Name]= '" + Me!
[Text15] + "'"
where = where & " AND [Date of Hire] Between " &
Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " &
Format
(Me![txtEnd], "\#m\/d\/yyyy\#") + "'"
where = where & " AND [Location]= '" + Me! [Text12]
+ "'"
where = where & " AND [Title]= '" + Me! [Combo23]
+ "'"
where = where & " AND [Union]= '" + Me! [Combo27]
+ "'"
[snip]


Man, am I dense. I completely missed your use of + to
sppress conditions that were not specified.
Unfortunately,
that will not work with Format since it always returns a
string. Use an If statement around the date range
Click to expand...
part
to
suppress the condition when nothing is specified in
txtStart
or txtEnd.

If Not ( IsNull(txtStart) Or IsNull(txtEnd) ) Then
where = where & " AND [Date of Hire] Between " _
& Format(Me![txtStart], "\#m\/d\/yyyy\#")
& " And " _
& Format(Me![txtEnd], "\#m\/d\/yyyy\#")
& "'"
End If
--
Marsh
MVP [MS Access]
.


.
Click to expand...
Click to expand...

.
Click to expand...
 
Thanks again! I've gotten all the navigation now done and
I could not have done it without your help!
Click to expand...

It's nice to hear that you're making some serious progress/

I really really appreciate your help.
Click to expand...

You're welcome.

Have a great day!
Click to expand...

I'm having a terrific day! You have one too.
--
Marsh
MVP [MS Access]


-----Original Message-----
It worked! Thank you so much! Only new problem. I was
playing with my switchboard and now it the switchboard
won't go away and i can't navigate to anything. I can't
bring up the database window or anything. what do I do so
that I can reset the settings on the switchboard?
Click to expand...

I may not be the best person to answer this one. In
general, you should start a new thread for a new topic.

All I can think of to try is to hold down the Shift key when
you open the database.
--
Marsh
MVP [MS Access]



-----Original Message-----
MaryAnn wrote:

Marsh,

Still getting runtime error 3075. syntax error in the
[Date of Hire] statement. HELP! And, you? Dense? I
hope
I grow to be dense too then. So in over my head right
now!

Thank you so much for all your continued help. I really
really appreciate it!

One missed item after another, what a day.

Remove the + "'" at the end of that statement.

If that doesn't fix it, post back with a Copy/Paste of
that
line of code and the result shown in the message box.
--
Marsh
MVP [MS Access]




-----Original Message-----

Marsh,

I named the text boxes txtStart and txtEnd. The error
I
am getting is as follows (in this example I put a name
in
the Last Name field:

Run-time error '3075':

Syntax error (missing operator) in query
expression '[LastName]='Smith' AND [Date of Hire]
Between
And ';'.

The code that is then highlighted (when I hit 'debug')
is:

Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
"Select * from EADDataForPhotos " & (" where " +
Mid
(where, 6) & ";"))


Also, here's the complete code I have....
[snip]

where = Null
where = where & " AND [Clock Number]= '" + Me!
[txtClock] + "'"
where = where & " AND [Last Name]= '" + Me!
[Text15] + "'"
where = where & " AND [Date of Hire] Between " &
Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " &
Format
(Me![txtEnd], "\#m\/d\/yyyy\#") + "'"
where = where & " AND [Location]= '" + Me!
[Text12]
+ "'"
where = where & " AND [Title]= '" + Me! [Combo23]
+ "'"
where = where & " AND [Union]= '" + Me! [Combo27]
+ "'"
[snip]


Man, am I dense. I completely missed your use of + to
sppress conditions that were not specified.
Unfortunately,
that will not work with Format since it always returns a
string. Use an If statement around the date range part
to
suppress the condition when nothing is specified in
txtStart
or txtEnd.

If Not ( IsNull(txtStart) Or IsNull(txtEnd) ) Then
where = where & " AND [Date of Hire] Between " _
& Format(Me![txtStart], "\#m\/d\/yyyy\#")
& " And " _
& Format(Me![txtEnd], "\#m\/d\/yyyy\#")
& "'"
End If
--
Marsh
MVP [MS Access]
.


.
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top