Question Return records based on date (1 Viewer)

[WayPay]

I don't think you can put that variable in the JOIN clause. Try this:

sSQL=  _
"SELECT AWI.exact_date, " & _
"    AWI.job_number, " & _
"    AWI.fitter_name, " & _
"    AWI.job_duration, " & _
"    fitters.fitter_phone " & _
"FROM AWI " & _
"    INNER JOIN fitters " & _
"    ON  AWI.fitter_id   = fitters.fitter_id " & _
"WHERE fitters.fitter_id = [Forms]![edit_job_awi]![tbfitter_id] " & _
"ORDER BY AWI.exact_date"

that is, if you have a fitter_id in you AWI table by now. Otherwise, JOIN on AWI.fitter_name = fitters.fitter_name.

 

[iidx036]

Hi Waypay,

Been away on business so I haven't had chance to reply. I couldn't get the SQL command to work at all using:

"fitters.fitter_id = [Forms]![edit_job_awi]![tbfitter_id]"

However, to get it to work I declared a string variable and used:

"fitters.fitter_id = " & fitter_id_var &"

And it worked - I thought your example should work though?

Anyway, finally its all sorted - thankyou once again for all yourhelp and support.

Jay

 

[namliam]

You cannot use a reference to a form if you are executing SQL in code, you have to replace the value just the way you did.

using the form reference will work in a query object / i.e. normal query.