SQL Select statement 1

[DajTwo]

I have been trying to find an example of an SQL select statement that I could incorporate but either im blind or dumb.. So I tried to construct it from books and internet finds but I get an Expected: end of Statement at the FROM command. Can you tell me where I can find examples or tutorials on how to do these select statements in VBA?

Thanks!!!


Access 2003

Dim db As Database
Dim rsAudNum As Recordset
Dim strSQL As String

ccan = cmbvalue
audit_generated_date = dateval

Set db = CurrentDb
Set rsAudNum = db.OpenRecordset("tbl_audit_gener")

strSQL = "SELECT tbl_audit_gener.[audit_#], tbl_audit_gener_ccan, tbl_audit_gener.audit_generated_date" _
FROM tbl_audit_gener WHERE (((tbl_audit.generated.ccan)= cmbvalue AND ((tbl_audit_gener.audit_generated_Date)= dateval))

 

[PHV]

strSQL = "SELECT [audit_#], ccan, audit_generated_date" _
& " FROM tbl_audit_gener WHERE ccan=" & cmbvalue & " AND audit_generated_Date=#" & dateval & "#"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DajTwo]

Thanks PH, sure does!

How can I store the value of audit_# in a variable? I have Dim statements for the ccan and datevalue earlier in the code but when I try to create a var for the audit_# field that the code you just corrected is retrieving, it gives me a not defined error.

Thanks again. Dont know what I would do without this site and you guys..

 

[PHV]

Dim var_audit
var_audit = DLookUp("[audit_#]", "tbl_audit_gener", "ccan=" & cmbvalue & " AND audit_generated_Date=#" & dateval & "#")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DajTwo]

Many thanks for your replies. I learned something and was to the point.

 

[DajTwo]

I Dim var_audit as Long (audit_# is a autonumber) and the 3443 Data taype mismatch error occurs. In the lookup table tbl_audit_gener I reverified the field formats and compared to the variables and for me all seem OK.

ccan is Text
audit_# is Long Integer
audit_generated_date is General date, which is generated by a now() command.

Dim cmbvalue As String 'For CCAN
Dim dateval As Date 'For date
Dim var_audit As Long 'For audit_#

Dim db As Database
Dim rsAudNum As Recordset
Set db = CurrentDb
Set rsAudNum = db.OpenRecordset("tbl_audit_gener")

var_audit = DLookup "[audit_#]", "[tbl_audit_gener]", "ccan=" & cmbvalue & " AND audit_generated_Date=#" & dateval & "#")

I looked on the net and found several example of 3464 but to no avail.

 

[PHV]

ccan is Text
var_audit = DLookup "[audit_#]", "[tbl_audit_gener]", "ccan=[!]'[/!]" & cmbvalue & "[!]'[/!] AND audit_generated_Date=#" & dateval & "#")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DajTwo]

Thanks again, I can see that now var_audit is = to the field looked up.

I will remember this lesson on quotes and formatting of dlookup..

Great Thanks!!