Text Entry for Query

[Nav4]

OK, first, I am no VBA or DBA. I have used DBIII and SBDIV ......some and have used Access for some limited useage, nothing complicated. I am trying to input to a query from a text box. If the input is left blank the the date fields functions as expected and limit the records for the dates between the two parameters. If I input from a drop down box the Crew Leader, a parameter inquiry comes up with the name from the drop down box. If I re-enter the name it works great. If I leave blank .....nothing. So.........I assume that the

stWhere = "[Crew Leader] =" & Me.CmbCl & " And "
blnTrim = True

is mis-typed, wrong syntax, or...................

Entire Sub:


Private Sub CmdGenRepSM_Click()
On Error GoTo Err_CmdGenRepSM_Click


Dim stDocName As String
Dim stWhere As String
Dim stDates As String
Dim blnTrim As Boolean

If Not IsNull(Me.CmbCl) Then
stWhere = "[Crew Leader] =" & Me.CmbCl & " And "
blnTrim = True
End If


If IsNull(Me.txtstartdate) And Me.txtstartdate = "" Then
If Not IsNull(Me.txtenddate) And Me.txtenddate <> "" Then
stWhere = stWhere & "[indate] <=" & Me.txtstartdate & "# and"
blnTrim = True
End If
Else
If IsNull(Me.txtenddate) And Me.txtenddate = "" Then
If Not IsNull(Me.txtstartdate) And Me.txtstartdate <> "" Then
stWhere = stWhere & "[indate]>=" & Me.txtstartdate & "# and "
blnTrim = True

End If
Else
If (Not IsNull(Me.txtstartdate) And Me.txtstartdate <> "") And (Not IsNull(Me.txtenddate) Or Me.txtenddate <> "") Then
stWhere = stWhere & "[indate] Between # " & Me.txtstartdate & "# And #" & Me.txtenddate & " # "
blnTrim = Ytue
End If
End If
End If

If blnTrim Then
stWhere = Left(stWhere, Len(stWhere) - 5)
End If
stDocName = "quesafetymeeting"
DoCmd.OpenReport stDocName, acPreview, , stWhere
Exit_CmdGenRepSM_Click:
Exit Sub

Err_CmdGenRepSM_Click:
MsgBox Err.Description
Resume Exit_CmdGenRepSM_Click

Nav4

 

[pbaldy]

It sounds like this is text, so try

stWhere = "[Crew Leader] = '" & Me.CmbCl & "' And "

 

[boblarson]

There's a whole lot more than that involved. Several errors including

stWhere = stWhere & "[indate] <=" & Me.txtstartdate & "# and"

needs a # before the quotes and a space after the word and

stWhere = stWhere & "[indate] <=#" & Me.txtstartdate & "# and "

And you are missing # in several other lines as well.

 

[pbaldy]

I didn't look at any of the rest of it, given that the OP said:

"the date fields functions as expected"

 

[Nav4]

Text for Query/Form

Well, I can't thnak both of you enough...............The first suggestion fixed what I was trying to do and I'll add the rest with further testing.

Please color me embarassed.

Again THANKS!

Nav4

 

[Nav4]

Text for Query/Form

Well, I can't thank both of you enough...............The first suggestion fixed what I was trying to do and I'll add the rest with further testing.

Please color me embarassed.

Again THANKS!

Nav4

(typo corrected)

 

[boblarson]

I didn't look at any of the rest of it, given that the OP said:

"the date fields functions as expected"

I don't think that they really were as it was incorrect in two of the three and I doubt that they really had it working as expected. Part of it should have been working but when the other conditions fire (which I'm not sure will ever fire due to the way they are worded) then the syntax would have been off because it would return a date as

12/14/2007#

and not

#12/14/2007# which is how it should.

 

[Nav4]

Gentlemen,

To both of you I owe a great big THANK YOU!

I now have several of the report generators work as I want them to, however; when I present this there'll be several more changes I am sure.

Honestly, Bob, the date functioned as expected. Before the changes you suggested. If I wanted data from Novemeber, I would select the date range and it would give me all inclusive from one date to another.

I have bowed to your wisdom and installed the corrections you recommended.

Again, Thanks...........The tree outside will appreciate me not banging my head on it quite so often!

Nav4

 

[ajetrumpet]

The tree outside will appreciate me not banging my head on it quite so often!

I'm not sure about that Nav, you better check with that specific tree. Some of them are quite lonely.

 

[boblarson]

Honestly, Bob, the date functioned as expected. Before the changes you suggested. If I wanted data from Novemeber, I would select the date range and it would give me all inclusive from one date to another.

Yes, the statement for when you had BOTH dates was fine and didn't need changes. It was the two statements for if one or the other was null that was missing the # and that is why I said that you might not have tested those and that they should have failed without the #.