sql wizard to sql vba

[mr moe]

guys, can someone help, i copied this sql from the wizard window, i'm trying to put the sql in a vba code here is what i did, the select is not working it has syntax, can someone help?

Dim mySQL As String
Dim db As Database
Dim rs As Recordset
Dim x As Long

Set db = CurrentDb

mySQL = "SELECT change_order_tbl.serial_num, Max(change_order_requestor_tbl.change_order_effective_date) AS MaxOfchange_order_effective_date" _
FROM change_order_requestor_tbl INNER JOIN change_order_tbl ON change_order_requestor_tbl.change_order_id=change_order_tbl.change_order_id
GROUP BY change_order_tbl.serial_num
HAVING (((change_order_tbl.serial_num)="abc"));"


Set rs = db.OpenRecordset(mySQL)

rs.MoveFirst

x = rs!countoffield

 

[boblarson]

Change it to:

mySQL = "SELECT change_order_tbl.serial_num, Max(change_order_requestor_tbl.change_order_effect ive_date) AS MaxOfchange_order_effective_date " & _
"FROM change_order_requestor_tbl INNER JOIN change_order_tbl ON change_order_requestor_tbl.change_order_id=change_ order_tbl.change_order_id " & _
"GROUP BY change_order_tbl.serial_num " & _
"HAVING (((change_order_tbl.serial_num)='abc'))"

 

[mr moe]

Thank you

Thanks sir.

 

[boblarson]

Glad we could help.

 

[mr moe]

Error Msg

Thanks for your help but i got this error msg when I tried to run it from a button, attached is the error msg, here is the full code,


Private Sub Command65_Click()
Dim mySQL As String
Dim db As Database
Dim rs As Recordset
Dim x As Long

Set db = CurrentDb

mySQL = "SELECT change_order_tbl.serial_num,change_order_tbl.location_to Max(change_order_requestor_tbl.change_order_effective_date) AS MaxOfchange_order_effective_date " & _
"FROM change_order_requestor_tbl INNER JOIN change_order_tbl ON change_order_requestor_tbl.change_order_id=change_ order_tbl.change_order_id " & _
"GROUP BY change_order_tbl.serial_num,change_order_tbl.location_to " & _
"HAVING (((change_order_tbl.serial_num)='abc'))"

this is where the error is------------------------------"""""
Set rs = db.OpenRecordset(mySQL)

rs.MoveFirst

MsgBox mySQL

End Sub

 

[boblarson]

Not sure as I don't see anything that jumps out at me.

 

[mr moe]

thanks

ok thanks sorry to bother you, but i'm trying to debug the problem, i put
msgbox change_order_effective_date but i'm getting blank msgbox?

 

[boblarson]

That may be that it is null. You might want to use the Nz function to test and fix any nulls.

 

[mr moe]

Last Question

ok Sir,
I promise that this is the last quesiton, I'm about to give up, maybe my english language is not helping, ok forget all the above comments, what method do u advise me to use, I have a form and I want to run a check using two table,
for example when I enter a serial number i want to push the button and have the code open the two tables and locate the record and do a comparison between the form data "i have just typed and the table. omg how can this be done. I wanna pull out my hair,