Complex Select statement syntax in VBA

Hello - I am having trouble with the syntax for a SELECT statement that involves selecting records between two dates and where the desired records meet two conditions that are characterized by strings.  The portion of the select statement that selects records between the two dates works fine all by itself, but when I try to add the string parameters I get a data type mismatch error.  The string parameters appear to be numbers, but they are actually strings.  They are strings assigned to states and counties. They are known as FIPS codes.  Each state has a FIPS code and each county within each state has a FIPS code.  Again, the values look like integers, but they are strings.  Here is the line of code that selects records that fall within two dates that works fine:
rst2.Open "Select * From [qry_NumberOfClosings_ByCounty] where clsng_dt between #" & dStartDate & "# AND #" & dEndDate & "#"

Here is the line with addition of the attempt to add the FIPS codes as additional parameters.  (BTW, the variable names are dimensioned correctly as strings):

rst2.Open "Select * From [qry_NumberOfClosings_ByCounty] where clsng_dt between #" & dStartDate & "# AND #" & dEndDate & "#" And """fips_st_cd = " & strStateFIPS & """" And """fips_cnty_cd =" & strCountyFIPS & """"

In this case, fips_st_cd and fips_cnty_cd are columns in the underlying query.  

Thanks very much in advance for your help.
Ron
rfreudAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
Give this a try:

rst2.Open "Select * From [qry_NumberOfClosings_ByCounty] where clsng_dt between #" & dStartDate & "# AND #" & dEndDate & "#" & " And fips_st_cd = '" & strStateFIPS & "' And fips_cnty_cd = '" & strCountyFIPS & "'"

Open in new window


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rfreudAuthor Commented:
Thanks a million for your accurate and quick assistance. I will try and learn from this.  Really really appreciate it.....
Ron
0
Bill PrewCommented:
Welcome Ron.


»bp
0
rfreudAuthor Commented:
This worked great!  Thanks very very much...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.