VBA Query problems

[larryg99]

Hello All;

I have been struggling with this query. I have tried what seem to be a thousand different combinations, but no luck getting it to run error free. Can anyone see what I am doing wrong ? I either get missing operator errors, or type mismatch errors.

dDateTicketAssigned is date format
dDateTicketCompleted is in date format
tb_dDate is date format.

This works in query builder:
SELECT tbl_WorkOrderTickets_Converted.*, tbl_WorkOrderTickets_Converted.dDateTicketAssigned, tbl_WorkOrderTickets_Converted.dDateTicketCompleted
FROM tbl_WorkOrderTickets_Converted
WHERE (((tbl_WorkOrderTickets_Converted.dDateTicketAssigned) Like [forms]![frm_AM_LoadOtherTickets]![tb_dDate] & "*") AND ((tbl_WorkOrderTickets_Converted.dDateTicketCompleted) Is Null));

This doesn't when converted to VBA SQL:
strSql = "SELECT tbl_WorkOrderTickets_Converted.*, tbl_WorkOrderTickets_Converted.dDateTicketAssigned, tbl_WorkOrderTickets_Converted.dDateTicketCompleted " & vbCrLf & _
"FROM tbl_WorkOrderTickets_Converted " & vbCrLf & _
"WHERE (((tbl_WorkOrderTickets_Converted.dDateTicketAssigned) Like [forms]![frm_AM_LoadOtherTickets]![tb_dDate] & ""*"") AND ((tbl_WorkOrderTickets_Converted.dDateTicketCompleted) Is Null));"

Also tried:
strSQL = "SELECT tbl_WorkOrderTickets_Converted.*, tbl_WorkOrderTickets_Converted.dDateTicketAssigned, tbl_WorkOrderTickets_Converted.dDateTicketCompleted" & _
"FROM tbl_AM_WorkstationTickets_Converted " & _
"WHERE ((([tbl_WorkOrderTickets_Converted.dDateTicketAssigned] = Like #[Forms]![frm_AM_LoadOtherTickets].[tb_dDate] & " * "#;"


Thanks...

 

[plog]

What is strSQL? And by that, don't assume you know what it is just by reading the VBA code. Have the code spit out strSQL after it done building it so you can see what it actually contains with your own eyes and then compare it to the SQL you posted that works.

 

[jdraw]

larry,

What are you trying to do in plain English?

You do not need the vbcrlf in your vba ---that's only for presentation/output.

Do you have Option Explicit at the top of your module?
As plog suggested use

 Debug.print strSQL

before you try to execute the code. This will show you how Access has understood and rendered your SQL.

See more debugging techniques in the links in my signature.
Good luck.

 

[larryg99]

Sorry for the assumption...

All code is behind a button on a form. Once the parameters of the form are selected by the user, the intent is to launch the query (VBA SQL) and return the results to an unbound form. The user has several options to chose from, with several text/combo boxes on the form. When the user selects one text/combo box, the rest go Enabled=False. One of the selections is to chose the workorders by date. That's the sql provided in the first post. Any other selection (text based) is already coded and is working fine. There is just something about the date fields that I am missing. Every change I have tried results in either an operator missing error, or type mismatch error. I have 3 other sqlSTR's behind the button, and all function properly.

Each sqlSTR is converted from sqlSTR (1 through4) to sqlSTR5 via If/Then statement, then kicked off by:
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset(strSQL5, dbOpenDynaset)

Again... all other sql strings function as desired.

Hope that helps...

Thanks...

 

[jdraw]

Do some debug.print of the date fields/values you suspect.

Do some code break/step through the code and investigate.

We (readers) do not know your application nor data. So it's up to you to do some debugging to narrow down the area of concern and resolve the "error/issue".

 

[larryg99]

Do some debug.print of the date fields/values you suspect.

Do some code break/step through the code and investigate.

We (readers) do not know your application nor data. So it's up to you to do some debugging to narrow down the area of concern and resolve the "error/issue".

Thank you for the response...

The date fields in the table are in "General" data format. The textbox field on the form is also date format, but "Short Date".

Not sure which "code" you are referring to. As indicated previously, ALL code behind the button functions properly, except of course the actual VBA SQL string in question here. My first post listed the SQL that works correctly being run as a stand-alone query in the query builder (QBE). However, when converted to a VBA SQL string, it fails (2 examples provided in OP). The error codes are either "missing operator", or "type mismatch" depending on where and how I manipulate the brackets, parens, quotes etc.

I have seen some posts that indicate to copy the sqlstr into the QBE and it will give indications of the problems. I have done that, and, when the issues indicated are fixed, the code will run... in the QBE. But when that very same code (listed in OP) is converted into a VBA SQL string (also listed in OP), it fails.

Thanks...

 

[static]

Your code seems to contain random spaces.

sql = "SELECT * FROM tbl_WorkOrderTickets_Converted"
sql = sql & " WHERE dDateTicketAssigned Like '" & [forms]![frm_AM_LoadOtherTickets]![tb_dDate] & "*'"
sql = sql & " AND dDateTicketCompleted Is Null"

 

[jdraw]

From your original post

strSql = "SELECT tbl_WorkOrderTickets_Converted.*, tbl_WorkOrderTickets_Converted.dDateTicketAssigned , tbl_WorkOrderTickets_Converted.dDateTicketComplete d " & vbCrLf & _
"FROM tbl_WorkOrderTickets_Converted " & vbCrLf & _
"WHERE (((tbl_WorkOrderTickets_Converted.dDateTicketAssig ned) Like [forms]![frm_AM_LoadOtherTickets]![tb_dDate] & ""*"") AND ((tbl_WorkOrderTickets_Converted.dDateTicketComple ted) Is Null));"

My guess is that this
[forms]![frm_AM_LoadOtherTickets]![tb_dDate] will be treated as text. I think what you want is to have Access derive and provide the value from the form to your sql.
so you need to ensure that you don't treat the form reference as text.

strSql = "SELECT tbl_WorkOrderTickets_Converted.*, tbl_WorkOrderTickets_Converted.dDateTicketAssigned , tbl_WorkOrderTickets_Converted.dDateTicketCompleted " & vbCrLf & _
"FROM tbl_WorkOrderTickets_Converted " & vbCrLf & _
"WHERE (((tbl_WorkOrderTickets_Converted.dDateTicketAssigned) Like " & _ 
[forms]![frm_AM_LoadOtherTickets]![tb_dDate] & "'*' ) AND ((tbl_WorkOrderTickets_Converted.dDateTicketCompleted) Is Null));"

untested -- but give it a try.
I don't think you can like * with a Date, think it has to be with text/string.

This should have shown up in the debug.print in my view. Perhaps I'm missing something from your post.

UPDATE: This post got submitted somehow??? I was working on a post and lost it. This is similar to
the post below but it's not a duplicate.

 

[jdraw]

What exactly does this mean??

This doesn't when converted to VBA SQL:
strSql = "SELECT tbl_WorkOrderTickets_Converted.*
, tbl_WorkOrderTickets_Converted.dDateTicketAssigned , tbl_WorkOrderTickets_Converted.dDateTicketComplete d " 
& vbCrLf & _
"FROM tbl_WorkOrderTickets_Converted " & vbCrLf & _
"WHERE (((tbl_WorkOrderTickets_Converted.dDateTicketAssig ned)
 Like [forms]![frm_AM_LoadOtherTickets]![tb_dDate] & ""*"") 
AND ((tbl_WorkOrderTickets_Converted.dDateTicketComple ted) Is Null));"

My guess is that in this SQL string, this part will be treated as text
[forms]![frm_AM_LoadOtherTickets]![tb_dDate]

What you want is for Access to get the value for the form value and place it in your SQL.
This should have been seen when you did a debug.print strSQL in my view.
The form info would show as a string.

Also, I don't think you can use Like * with a real Date.

If the form control is set up as follows

"WHERE (((tbl_WorkOrderTickets_Converted.dDateTicketAssigned) Like " & [forms]![frm_AM_LoadOtherTickets]![tb_dDate] & ""*"") then you should get the value from the form.

But as I said, I don't think you are working with a Date data type.


I'm having difficulty with laptop or site????

 

[Galaxiom]

Also, I don't think you can use Like * with a real Date.

Dates are implicitly cast to strings when the Like operator is applied to them.

However it isn't an efficient way to query by date. Better to use Between two actual dates.

 

[larryg99]

From your original post

strSql = "SELECT tbl_WorkOrderTickets_Converted.*, tbl_WorkOrderTickets_Converted.dDateTicketAssigned , tbl_WorkOrderTickets_Converted.dDateTicketCompleted " & vbCrLf & _
"FROM tbl_WorkOrderTickets_Converted " & vbCrLf & _
"WHERE (((tbl_WorkOrderTickets_Converted.dDateTicketAssigned) Like [forms]![frm_AM_LoadOtherTickets]![tb_dDate] & ""*"") AND ((tbl_WorkOrderTickets_Converted.dDateTicketCompleted) Is Null));"

My guess is that this
[forms]![frm_AM_LoadOtherTickets]![tb_dDate] will be treated as text. I think what you want is to have Access derive and provide the value from the form to your sql.
so you need to ensure that you don't treat the form reference as text.

strSql = "SELECT tbl_WorkOrderTickets_Converted.*, tbl_WorkOrderTickets_Converted.dDateTicketAssigned , tbl_WorkOrderTickets_Converted.dDateTicketCompleted " & vbCrLf & _
"FROM tbl_WorkOrderTickets_Converted " & vbCrLf & _
"WHERE (((tbl_WorkOrderTickets_Converted.dDateTicketAssigned) Like " & _ 
[forms]![frm_AM_LoadOtherTickets]![tb_dDate] & "'*' ) AND ((tbl_WorkOrderTickets_Converted.dDateTicketCompleted) Is Null));"

untested -- but give it a try.
I don't think you can like * with a Date, think it has to be with text/string.

This should have shown up in the debug.print in my view. Perhaps I'm missing something from your post.

UPDATE: This post got submitted somehow??? I was working on a post and lost it. This is similar to
the post below but it's not a duplicate.

JDraw... thanks for the reply.

Getting close I think. Your suggestion produced the following error:

Run-time error 3075
Syntax error (missing operator) in query expression (((tbl_WorkOrderTickets_Converted.dDateTicketAssigned) Like 12/6/2016'*'
AND ((tbl_WorkOrderTickets_Converted.dDateTicketCompleted) Is Null));"

The good part is its now picking up the date from the form. I moved the {((tbl_WorkOrderTickets_Converted.dDateTicketCompleted) Is Null));"} to read: {(Is Null(tbl_WorkOrderTickets_Converted.dDateTicketCompleted)));"} but such resulted in the same error.

==> "This should have shown up in the debug.print in my view." ... I must be doing something wrong because it seems no matter where I place a debug.print statement in the code, nothing happens. If the sql string errors out, how can the debug.print produce anything? I'm expecting either data to appear in the intermediate window, or the sql string itself with perhaps some portion of it highlighted. However as I referenced... the sql string itself errors out. Also, once it errors out, and I enter debug mode, hovering over the highlighted string produces nothing. Normally in a error/debug situation I would see what the variables are indicating, including nothing if that's the case.

Thanks again.

p.s. I don't know why but posting the code inserts indiscriminate spaces, such as ".dDateTicketCom pleted" - there are NO spaces in my actual code.