s.o.s please! something odd happend

[icemonster]

Function startRowSourceZipCode()

    strStartSql1 = "SELECT qry_city_state_zipcode_01.id_us_zip_code, " _
                    & "qry_city_state_zipcode_01.uszipcod_city, " _
                    & "qry_city_state_zipcode_01.usstacod_abbreviation, " _
                    & "qry_city_state_zipcode_01.usstacod_state_name " _
                    & "FROM qry_city_state_zipcode_01 "
                   
    strWhereSql1 = "WHERE qry_city_state_zipcode_01.uszipcod_city LIKE '*" & Me.cbocity & "*'"
    
    strSortOrderSql1 = "ORDER BY qry_city_state_zipcode_01.id_us_zip_code ;"
    
    strSQL1 = strStartSql1 & strWhereSql1 & strSortOrderSql1
    
    With Me.cbozipcode
           .RowSource = strSQL1
           .Value = Null
           
    End With

End Function

it's so odd, about an hour ago it was working. what this does is when the city is selected on the combo box (cbocity) it will filter the zip codes necessary for it. any ideas why it stopped? rebooted, restarted and even re typed the code.

 

[icemonster]

basically the problem is the where clause suddenly stopped working.

 

[PNGBill]

You need a space in the start of your string.

strWhereSql1 = " WHERE qry_city_state_zipcode_01.uszipcod_city LIKE '*" & Me.cbocity & "*'"

Other lines will also require this space.

 

[icemonster]

do you mind telling me why i need the space for? actually i got it to work, i removed the * on the where clause but odd thing is on another app it's exactly written the way it is with the same exact settings and about 4 or 6 hours ago this function was working fine.

 

[PNGBill]

When you concatenate sql in vba you need a space of at least one chr.
This can be in the preceeding line or the following line.

strStartSql1 has the space.

strWhereSql1 does not have a space on either the beginning or the end of the line.

strSortOrderSql1 does not have a space at the beginning.

This means your

strSQL1 = strStartSql1 & [COLOR=red]strWhereSql1 & strSortOrderSql1
[/COLOR]

Will fail for the 2nd concatenation (red)