Access VBA SQL where clause issue

Question

I need some help. When i run the below query I get no results back if i include the Pkg number part of the where. When i run the query in Access it works fine. example of vars package number 1_282 Rptdt 201301

Dim db As Database 'generic database object
Dim rst As Recordset 'this is going to hold the query result
Set db = CurrentDb
Dim PKG As Double
Dim rptDT As Double
Dim wireDT As Date

Set rst = db.OpenRecordset("SELECT Max(tbl_Revision.Revision_Number) as Revision_Number FROM tbl_Revision" & _
" where (tbl_Revision.RUN_YR_MO=" & rptDT & ")" & _
" and (tbl_Revision.Package_Number=" & PKG & ")")

getRevision = rst!Revision_Number + 1

what type tbl_Revision.Package_Number is ?

4dmonster
– 4dmonster

2013-09-27 11:28:41 +00:00
Commented Sep 27, 2013 at 11:28 — 4dmonster
– 4dmonster, Commented Sep 27, 2013 at 11:28
The package_number is a text field in the table.

James.l.Norman
– James.l.Norman

2013-09-27 12:59:54 +00:00
Commented Sep 27, 2013 at 12:59 — James.l.Norman
– James.l.Norman, Commented Sep 27, 2013 at 12:59

Wietze314 · Accepted Answer · 2013-09-27 13:51:35Z

3

The PKG cant be a Double if you want 1_282 to work. So make it a string. Also you will have to add quotes :

" where (tbl_Revision.RUN_YR_MO='" & rptDT & "')" & _

answered Sep 27, 2013 at 13:51

Wietze314

6,0202 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James.l.Norman Over a year ago

" and (tbl_Revision.Package_Number=" & PKG & ")")

Collectives™ on Stack Overflow

Access VBA SQL where clause issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related