SQL help on syntax (1 Viewer)

expat1000 · Sep 18, 2014

Hi,

I'm setting query SQL dynamically from a search form. Typical output is like this:

Code:

qry.SQL = "SELECT DISTINCT dpay.PRO_NO, dpay.PKDATE, dpay.FROM, dpay.DPDATE, dpay.TO, dpay.DHMILE, dpay.LDMILE, dpay.MITOT, dpay.STOPTOT, dpay.UNLOAD, dpay.HOURS," & _
                  "dpay.HRTOT, dpay.SUBTOT, dpay.STOPS, dpay.tarptot, dpay.dettot, dpay.misctot, dpay.TOTPAY, dpay.SS_NO, tload.LOAD_TEMP, Driver.DRV_CODE," & _
                  " FROM Driver RIGHT JOIN (dpay INNER JOIN tload ON dpay.PRO_NO = tload.PRO_NO) ON Driver.SS_NO = dpay.SS_NO"

I need to refine it to filter out PRO_NO that do not contain a decimal. I'm thinking of using the instr() function but can't get the quotes right. For example, adding this

Code:

qry.SQL = qry.SQL & " AND " & InStr(ProNO, ".") = 0

gives me an error (naturally) that Pro_No is not variable. I've tried many other ways of quoting the function parts and arguments without success

I'm not even sure I can do this, or do I have to create a 2nd query with the filter? Due to other factors, that would make things much more complex..

jdraw · Sep 18, 2014

Is it ProNo or Pro_No? Spelling may be the issue.

expat1000 · Sep 18, 2014

jdraw said:
Is it ProNo or Pro_No? Spelling may be the issue.

Thanks, but no. Just a typo. The quoting I used is clearly wrong. I just don't know what is right.

plog · Sep 18, 2014

qry.SQL = qry.SQL & " AND " & InStr(ProNO, ".") = 0

I believe you want the InStr call inside the quotes, not outside. As it is, it requires you have a variable in your VBA code called ProNO. And even still its syntatically incorrect because this:

InStr(ProNO, ".") = 0

isn't valid vba--you do the comparison outside of the string you are building so VBA tries to do that comparison--it doesn't get inserted into your SQL string.

That line probable needs to be this:

qry.SQL = qry.SQL & " AND & InStr(Driver.ProNO, '.') = 0"

expat1000 · Sep 18, 2014

plog said:
I believe you want the InStr call inside the quotes, not outside. As it is, it requires you have a variable in your VBA code called ProNO. And even still its syntatically incorrect because this:

InStr(ProNO, ".") = 0

isn't valid vba--you do the comparison outside of the string you are building so VBA tries to do that comparison--it doesn't get inserted into your SQL string.

That line probable needs to be this:

qry.SQL = qry.SQL & " AND & InStr(Driver.ProNO, '.') = 0"

There is no driver.ProNO field, nor any ProNO field in the source. Using tload.pro_no gives this error. Note I ran the calling code with simpler criteria.

Code:

Syntax error (missing operator) in query expression 'PKDATE Between #03/02/2014# And #04/30/2014# AND & InStr(tload.PRO_NO, '.') = 0

plog · Sep 18, 2014

Remove the ampersand before the Instr call.

namliam · Sep 18, 2014

Yes, readable code is maintainable code

Code:

qry.SQL = "SELECT DISTINCT dpay.PRO_NO " & _
               ", dpay.PKDATE " & _
               ", dpay.FROM " & _
               ", dpay.DPDATE " & _
               ", dpay.TO " & _
               ", dpay.DHMILE " & _
               ", dpay.LDMILE " & _
               ", dpay.MITOT " & _
               ", dpay.STOPTOT " & _
               ", dpay.UNLOAD " & _
               ", dpay.HOURS " & _
               ", dpay.HRTOT " & _
               ", dpay.SUBTOT " & _
               ", dpay.STOPS " & _
               ", dpay.tarptot " & _
               ", dpay.dettot " & _
               ", dpay.misctot " & _
               ", dpay.TOTPAY " & _
               ", dpay.SS_NO " & _
               ", tload.LOAD_TEMP " & _
               ", Driver.DRV_CODE, " & _
         " FROM           Driver  " & _
         " RIGHT JOIN (   dpay  " & _
         " INNER JOIN tload ON dpay.PRO_NO = tload.PRO_NO " & _
                     " ) ON Driver.SS_NO = dpay.SS_NO "

No Where ?

expat1000 · Sep 19, 2014

plog said:
Remove the ampersand before the Instr call.

I should have caught that. That works. Thanks, all!

SQL help on syntax (1 Viewer)

expat1000

Registered User.

jdraw

Super Moderator

expat1000

Registered User.

plog

Banishment Pending

expat1000

Registered User.

plog

Banishment Pending

namliam

The Mailman - AWF VIP

expat1000

Registered User.

Users who are viewing this thread