Help with code

  • Thread starter Thread starter DevilDog1978
  • Start date Start date
D

DevilDog1978

I am trying to get this to work:

Select [pcinventrySQL.*]
IF ME! [sub_cust] Like "QUQ" Or "CPQ*" Or "NRQ" Or "NCQ" Or "BBQ" Or "QJQ"
Or "WDQ" Or "KWQ" Or "MBQ" Or "PBQ" Or "PCQ" Or "NOL" Or "ENQ" Or "ESQ" Or
"VIQ" Or "RPQ" Or "UBQ" Or "GBQ" Or "NGQ" Or "PAQ" Or "WWQ" Or "KRQ" Or "JJQ"
Or "IJQ" Or "DLQ" Or "QDQ" Or "QHQ" Or "QLQ" Or "SHQ" Or "FLQ" Or "LBQ" Or
"PMQ" Or "YLQ" Or "MNQ" Or "JVQ" Or "RNQ" Or "TBQ" Or "CBQ" Or "QYQ" Or "NIQ"
Or "AEQ" Or "WCQ" Or "ATQ" Or "QAQ" Or "QEQ" Or "GSQ" Or "QCQ" Or "EXQ" Or
"AGX" Or "CCQ" Or "CCX" Or "DSX" Or "AKF" Or "AKI" Or "JAQ" Or "RFX" Or "LAL"
Or "MAR" Or "PRL" Or "SDB" Or "MIQ" Or "CPB" Or "IGQ" Or "ICE" Or "CAB" Or
"SBA" Or "9KQ" Or "9LN" Or "JFB" Or "9MQ" Or "NEQ" Or "9OQ" Or "SDP" Or "9WI"
Or "MCC" Or "PCC" Or "BAM" Or "AGC" Or "OXX" Or "OBL" Or "NCS" Or "WPP" Or
"*CASS" Or "*PEND"
UPDATE ME! [std_cal] = "Y"
Else End If;

Any suggestions?
 
DevilDog1978 said:
I am trying to get this to work:

Select [pcinventrySQL.*]
IF ME! [sub_cust] Like "QUQ" Or "CPQ*" Or "NRQ" Or "NCQ" Or "BBQ" Or
"QJQ"
Or "WDQ" Or "KWQ" Or "MBQ" Or "PBQ" Or "PCQ" Or "NOL" Or "ENQ" Or "ESQ" Or
"VIQ" Or "RPQ" Or "UBQ" Or "GBQ" Or "NGQ" Or "PAQ" Or "WWQ" Or "KRQ" Or
"JJQ"
Or "IJQ" Or "DLQ" Or "QDQ" Or "QHQ" Or "QLQ" Or "SHQ" Or "FLQ" Or "LBQ" Or
"PMQ" Or "YLQ" Or "MNQ" Or "JVQ" Or "RNQ" Or "TBQ" Or "CBQ" Or "QYQ" Or
"NIQ"
Or "AEQ" Or "WCQ" Or "ATQ" Or "QAQ" Or "QEQ" Or "GSQ" Or "QCQ" Or "EXQ" Or
"AGX" Or "CCQ" Or "CCX" Or "DSX" Or "AKF" Or "AKI" Or "JAQ" Or "RFX" Or
"LAL"
Or "MAR" Or "PRL" Or "SDB" Or "MIQ" Or "CPB" Or "IGQ" Or "ICE" Or "CAB" Or
"SBA" Or "9KQ" Or "9LN" Or "JFB" Or "9MQ" Or "NEQ" Or "9OQ" Or "SDP" Or
"9WI"
Or "MCC" Or "PCC" Or "BAM" Or "AGC" Or "OXX" Or "OBL" Or "NCS" Or "WPP" Or
"*CASS" Or "*PEND"
UPDATE ME! [std_cal] = "Y"
Else End If;

Any suggestions?
Click to expand...


That's a mixture of VBA procedural code and SQL, with errors in both, I'm
afraid. But you can't mix VBA and SQL like that, so what are you trying to
do, exactly? Are you trying to create an update query, to run once and
update a bunch of records in a table? That would call for SQL. Or are you
trying to do something to the current record on a form? That would call for
a VBA solution.
 
I apologize, I have struggled learning code. Basically, I want the std_cal
field update to "Y" if the sub_cust is listed (as below). All those codes for
sub-cust have to be entered like that because wild cards will introduce other
sub_custs that should not be present. Thanks for any help you can offer.

Dirk Goldgar said:
DevilDog1978 said:
I am trying to get this to work:

Select [pcinventrySQL.*]
IF ME! [sub_cust] Like "QUQ" Or "CPQ*" Or "NRQ" Or "NCQ" Or "BBQ" Or
"QJQ"
Or "WDQ" Or "KWQ" Or "MBQ" Or "PBQ" Or "PCQ" Or "NOL" Or "ENQ" Or "ESQ" Or
"VIQ" Or "RPQ" Or "UBQ" Or "GBQ" Or "NGQ" Or "PAQ" Or "WWQ" Or "KRQ" Or
"JJQ"
Or "IJQ" Or "DLQ" Or "QDQ" Or "QHQ" Or "QLQ" Or "SHQ" Or "FLQ" Or "LBQ" Or
"PMQ" Or "YLQ" Or "MNQ" Or "JVQ" Or "RNQ" Or "TBQ" Or "CBQ" Or "QYQ" Or
"NIQ"
Or "AEQ" Or "WCQ" Or "ATQ" Or "QAQ" Or "QEQ" Or "GSQ" Or "QCQ" Or "EXQ" Or
"AGX" Or "CCQ" Or "CCX" Or "DSX" Or "AKF" Or "AKI" Or "JAQ" Or "RFX" Or
"LAL"
Or "MAR" Or "PRL" Or "SDB" Or "MIQ" Or "CPB" Or "IGQ" Or "ICE" Or "CAB" Or
"SBA" Or "9KQ" Or "9LN" Or "JFB" Or "9MQ" Or "NEQ" Or "9OQ" Or "SDP" Or
"9WI"
Or "MCC" Or "PCC" Or "BAM" Or "AGC" Or "OXX" Or "OBL" Or "NCS" Or "WPP" Or
"*CASS" Or "*PEND"
UPDATE ME! [std_cal] = "Y"
Else End If;

Any suggestions?
Click to expand...


That's a mixture of VBA procedural code and SQL, with errors in both, I'm
afraid. But you can't mix VBA and SQL like that, so what are you trying to
do, exactly? Are you trying to create an update query, to run once and
update a bunch of records in a table? That would call for SQL. Or are you
trying to do something to the current record on a form? That would call for
a VBA solution.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
DevilDog1978 said:
I apologize, I have struggled learning code. Basically, I want the std_cal
field update to "Y" if the sub_cust is listed (as below). All those codes
for
sub-cust have to be entered like that because wild cards will introduce
other
sub_custs that should not be present. Thanks for any help you can offer.
Click to expand...

I'm sorry, but that still doesn't answer the main question: are you looking
to make this change to the current record on a form (a VBA solution), or to
run an update against a lot of records in a table?
 
against a lot of records in a table

Dirk Goldgar said:
I'm sorry, but that still doesn't answer the main question: are you looking
to make this change to the current record on a form (a VBA solution), or to
run an update against a lot of records in a table?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
DevilDog1978 said:
against a lot of records in a table
Click to expand...

Okay, that means we are looking for the correct SQL for an update query.
You want to update the std_cal field in table pcinventrySQL to "Y" when the
sub_cust field meets any of a list of criteria, some of which involve
wild-card matching. Is that correct? If so, the SQL would be something
like this:

UPDATE pcinventrySQL SET std_cal = "Y" WHERE
(sub_cust In ("QUQ", "NRQ", "NCQ", "BBQ", "QJQ", "WDQ",
"KWQ", "MBQ", "PBQ", "PCQ", "NOL", "ENQ", "ESQ", "VIQ",
"RPQ", "UBQ", "GBQ", "NGQ", "PAQ", "WWQ", "KRQ", "JJQ",
"IJQ", "DLQ", "QDQ", "QHQ", "QLQ", "SHQ", "FLQ", "LBQ",
"PMQ", "YLQ", "MNQ", "JVQ", "RNQ", "TBQ", "CBQ", "QYQ",
"NIQ", "AEQ", "WCQ", "ATQ", "QAQ", "QEQ", "GSQ", "QCQ",
"EXQ", "AGX", "CCQ", "CCX", "DSX", "AKF", "AKI", "JAQ",
"RFX", "LAL", "MAR", "PRL", "SDB", "MIQ", "CPB", "IGQ", "ICE",
"CAB", "SBA", "9KQ", "9LN", "JFB", "9MQ", "NEQ", "9OQ",
"SDP", "9WI"))
OR
(sub_cust Like"CPQ*" )
OR
(sub_cust Like"*CASS" )
OR
(sub_cust Like"*PEND" )

That is, if I read your original messsage correctly.

I'm not sure if there is a limit on the In() operator, such that the above
list of values is too long for it. You'll have to try it and see. Of
course, as with any code or SQL you aren't sure of, make a backup before
trying it.

It seems to me that a long list of values like that might be better stored
in a table, and the SQL rewritten to use that table to determine which
records to update. It would be easier to maintain and edit the table, if
you were going to be doing this sort of thing more than once.
 
That is in the ballpark of what I want. I created an update query using your
code, but it does not update the std_cal field to a Y. This is a query that
will be run monthly. If I am understanding you correctly, I can create a
table with all the lab codes and then use the SQL to pull those lab codes
from the table?
 
DevilDog1978 said:
That is in the ballpark of what I want. I created an update query using
your
code, but it does not update the std_cal field to a Y.
Click to expand...

When you say, "it does not update the std_cal field to a Y," what exactly
does it do? Do you get an error message? Does it update any records, or
does it find no records that match the criteria?

I believe I see a minor syntax error that I made in the original SQL -- I
needed a space after each use of the "Like" keyword -- but I believe it
should still have executed correctly. If it didn't update any records but
had no error, then it seems like no records met the criteria. Is that
conceivable? Can you cite an example of a record that should have been
updated but wasn't?

What is the data type (in the field design) of the std_cal field? From your
example, I assumed it was Text, but maybe it's a yes/no (boolean) field, in
which case you would have wanted to set it to True, not "Y".
This is a query that
will be run monthly. If I am understanding you correctly, I can create a
table with all the lab codes and then use the SQL to pull those lab codes
from the table?
Click to expand...

Probably, but this is the first time you've used the term "lab codes", so I
can't be sure. I'm guessing that these "lab codes" are stored in the
sub_cust field, but you're expecting a higher level of psychic ability than
I can reliably exert. <g> Anyway, let's first get a hard-coded query that
works, and then transform that into a table-driven one.
 
I actually messed it up when I pasted it in. It works fine thank you. Your
psychic abilities are amazing. Lab codes are that populate the sub_cust
field. I appreciate all your help on his. If I can make this table driven
that would be awesome.
 
DevilDog1978 said:
I actually messed it up when I pasted it in. It works fine thank you. Your
psychic abilities are amazing. Lab codes are that populate the sub_cust
field. I appreciate all your help on his. If I can make this table driven
that would be awesome.
Click to expand...

Okay, it's not so hard. Make a table that will contain the lab codes, or
lab code *patterns* including wild-card characters, that will be used to
identify the records to be updated. For example, you could create a table
named tblLabCodesToUpdate, with one text field, LabCode. Make that field
the primary key of the table. Into this table, enter as a new record each
of the lab codes or lab code patterns that you used in the SQL statement we
previously made. Here's a list I made:

*CASS
*PEND
9KQ
9LN
9MQ
9OQ
9WI
AEQ
AGX
AKF
AKI
ATQ
BBQ
CAB
CBQ
CCQ
CCX
CPB
CPQ*
DLQ
DSX
ENQ
ESQ
EXQ
FLQ
GBQ
GSQ
ICE
IGQ
IJQ
JAQ
JFB
JJQ
JVQ
KRQ
KWQ
LAL
LBQ
MAR
MBQ
MIQ
MNQ
NCQ
NEQ
NGQ
NIQ
NOL
NRQ
PAQ
PBQ
PCQ
PMQ
PRL
QAQ
QCQ
QDQ
QEQ
QHQ
QJQ
QLQ
QUQ
QYQ
RFX
RNQ
RPQ
SBA
SDB
SDP
SHQ
TBQ
UBQ
VIQ
WCQ
WDQ
WWQ
YLQ

You should be able to copy the above list and paste-append it into
tblLabCodesToUpdate.

Now make a new query. Switch to SQL View and paste in the following SQL:

UPDATE pcinventrySQL INNER JOIN tblLabCodesToUpdate
ON pcinventrySQL.sub_cust Like tblLabCodesToUpdate.LabCode
SET pcinventrySQL.std_cal = "Y"

Save that query with some meaningful name; maybe (for example)
"qryUpdatePcinventryStdCal". Now, every time you run that query, it should
update every record in pcinventrySQL whose sub_cust matches (is "Like") one
of the codes in tblLabCodesToUpdate. You can add, remove, or modify the
records in tblLabCodesToUpdate to control the selection of records to be
updated.
 
Back
Top