• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 28
  • Last Modified:

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
0
rfreud
Asked:
rfreud
  • 2
  • 2
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now