Access 2007 query to fill a textbox 1

[chromarog]

I've googled and searched and I'm ready to give up. I never thought it would be this tough.

All I want is to click a button to fire a query and have the result end up in a textbox. If the project wasn't going to build on itself and grow, I would look into Dlookup and such, I can't seem to find out if it's easier that way or not. Anyway, here is the code. Please tell me what the heck I'm doing wrong. All I get is Data Mismatch Errors. I've tried so many different things to change the datatypes, I can't even see straight.
Thanks,
Rog...

Private Sub BTNSetup_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim SUSQL As String

Set db = CurrentDb

SUSQL = "SELECT DISTINCTROW Sum(PTQ_VSMP2_Breakdown.REGHRS) " & _
  "FROM PTQ_VSMP2_Breakdown " & _
  "WHERE (((PTQ_VSMP2_Breakdown.WOTYPE)='SU') AND ((PTQ_VSMP2_Breakdown.COMPLETIONDATE) Between '" & StartDay & "' And '" & EndDay & "')) " & _
  "GROUP BY PTQ_VSMP2_Breakdown.WOTYPE; "
  
Set rst = db.OpenRecordset(SUSQL)

While Not rst.EOF
Me.Text18.Value = CStr(rst.Fields(0))

rst.MoveNext

Wend
rst.Close
db.Close
Set db = Nothing

End Sub

 

[SkipVought]

Hi,

What are the values of StartDay & EndDay?

Do a Debug.Print SUSQL

and post back with the results from the Immediate Window.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[chromarog]

All I get is the same message I've been getting.

Run Time error 3464
Data Type mismatch in criteria expression.

And when I click the debug button this line is highlighted.
Set rst = db.OpenRecordset(SUSQL)

 

[SkipVought]

I'm going to have to GUESS at the answers to the questions you ignored. This only delays the possiblity of getting a good answer.

I'm GUESSING that StartDay & EndDay are strings. I HOPE that you have structured them unambiguously as yyyy/mm/dd. Add the ## delimiters that will CONVERT the string to a date, as I'm GUESSING that COMPLETIONDATE is a REAL DATE.

SUSQL = "SELECT DISTINCTROW Sum(PTQ_VSMP2_Breakdown.REGHRS) " & _
  "FROM PTQ_VSMP2_Breakdown " & _
  "WHERE (((PTQ_VSMP2_Breakdown.WOTYPE)='SU') AND ((PTQ_VSMP2_Breakdown.COMPLETIONDATE) Between #" & StartDay & "# And #" & EndDay & "#)) " & _
  "GROUP BY PTQ_VSMP2_Breakdown.WOTYPE; "

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[chromarog]

Oops. Sorry, missed the first part of the question. I got all confused when the debug came up bupkiss. But thanks for the solution. It worked!! I had tried the delimiters but I had them enclosed withing the single quotes of my original code.

For future reference, is something like this corrected by forcing the properties of the textbox to a date format, Or in the VB? The original data from the server is a date. I guess it didn't have any issues getting information to it because it was getting data through: Me.StartDay = Me.MonthCombo.Column(1), simple but effective.

Thanks again Skip.
Rog...

 

[SkipVought]

"For future reference, is something like this corrected by forcing the properties of the textbox to a date format, Or in the VB?"

The Textbox returns, well, er, uh, TEXT. The TEXT, the whole TEXT and nothing but the TEXT, so help me Rog. So in order to use the TEXT in a query that needs a DATE, you must force a conversion IN THE SQL. Hence the ## delimiter. BTW, dates are really PURE NUMBERS.

faq68-5827


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]