Select Statement help with where clause

[ziggs]

Here's my current Select Statement:

SQL1 = "SELECT * From tbl_Business "
SQL1 = SQL1 & " Where ([Street Number] = " & Clng(Request.QueryString("dig&quot) & &quot AND"
SQL1 = SQL1 & " ([Street Direction] = '" & Replace(Request.QueryString ("dir&quot, "'", "''&quot & "') AND"
SQL1 = SQL1 & " ([Street Name] = '" & Replace(Request.QueryString ("street&quot, "'", "''&quot & "') AND"
SQL1 = SQL1 & " ([Street Type] = '" & Replace(Request.QueryString ("type&quot, "'", "''&quot & "') AND"
SQL1 = SQL1 & " ([aptsuite] = '" & Replace(Request.QueryString ("apt&quot, "'", "''&quot & "')"

Now, the 2nd database that I'll be drawing off of has the location format of three parts instead of the five parts as seen above. Thus, I need to create a Where statment that will combine the three in the middle (above) into one.

In other words, the Street Direction, Street Name, and Street Type, must be combined into one. Any ideas?

 

[link9]

Combined into one what? I don't get it...

What does the "combined" field look like in the second database??? comma separated or something?

 

[ziggs]

Sorry for the confusion. My other database will populate the Request Querystrings since this is running off of another ASP. However, the same ASP let's you pick your own address.

Anyway, this is what I did. I was able to create a formula and called it FullStreet. Fullstreet will take Street Dir, Street Name, and Street type and combined them to one name (Example. S is dir, Abel is street, St is type. thus, fullstree = S Able St). Now, is it possile to apply to Select statement? I've never done a combination where clause before that combines 3 fields to one.

Here's the middle section for the full street:

SQL1 = SQL1 & " ([Street Direction] = '" & Replace(Request.QueryString ("dir&quot, "'", "''&quot & "') AND"
SQL1 = SQL1 & " ([Street Name] = '" & Replace(Request.QueryString ("street&quot, "'", "''&quot & "') AND"
SQL1 = SQL1 & " ([Street Type] = '" & Replace(Request.QueryString ("type&quot, "'", "''&quot & "') AND"


I need the where clause to combine the [Street Direction], [Street Name] and [Street type] to one field and then use the fullstreet formula against it. I'm probably thinking about how to do this the wrong way though. Any thoughts would be appreciated. Thanks