Update Query 1

[DarkOne72]

Hello all,

I am trying to build an update query to do the following:
Keep in mind that the query will be launched by a button on a form.

I have a table named tblCompleted; when the data is put in all the data is there except 1 column named DISTANCE. Now what I need the update query to do is once the button is clicked it checks the CODE column (in the same table) which would be "B1" or "B2" or "B3" etc. I need it to update the tblCompleted table (same table) to something like "300-700 FT" but only new records and not mess with the records that are 100% (have both code and distance already there)

example: (before clicking the update button)

CODE DISTANCE
B3


(After clicking the button to run update query)

CODE DISTANCE
B3 300-700 FT

I hope this makes sense and someone can assist me!

Thanks in advance

 

[JBinQLD]

G'day fella,

Break it into simple steps.

a) make a query that displays only the data you want to update. Eg. Criteria would be

=Like "B*", [distance]=Is Null

b) Change query to become an update query

c) In the "update to" field of [DISTANCE] enter "300-700ft"

d) Save query as Eg. qryFixDistance

e) On your form add button logic:

[code]
docmd.setwarnings=false
    docmd.openquery "qryFixDistance"
docmd.setwarnings=true

This will do what you asked but It would be interesting to know what you're actual goal is because it's bad practise to "Hard code" values such as "Like B*" and "300-700ft" but you can investigate making it more flexible via funky function or whatever later eh?

Have a great weekend, JB

 

[JBinQLD]

oops! Sorry about formatting above, i put your field name 'code' in [ ] without thinking and that triggered the parser!

I'm sure you can see where i was going though!

 

[DarkOne72]

Thank for the great response!
I still don't understand, if there is a B3 i want it to do the 300-700 FT like you helped with but what if there is a B1 which would be different than the 300-700 FT. Here are the codes and the update text that needs to go with them:

B1 >300 FT
B2 >300 FT
B3 300-700 FT
B4 700> FT
B5 (-----nothing at all, blank)
B6 (-----nothing at all, blank)

Thanks again

 

[JBinQLD]

OK, let's move away from your hard coded values. Make a table called:

[tblBLookups]
BLookupID-Autonumber
BCode-text
BDistance-text (All zero length=true)

Save the table and populate as required:
BCode BDistance
B1 >300 FT
B2 >300 FT
B3 300-700 FT
B4 700> FT
B5 (-----nothing at all, blank)
B6 (-----nothing at all, blank)

Open your query "qryFixDistance" in design view. Add the [tblBLookups] we just made. Create a link between [tblBlookups].[BCode] and code field in your exising table.

In the "update to" field of [DISTANCE] enter [tblBLookups].[BDistance]. Remove the Like "B*" criteria

There you go, you now have a more future proof method - any new default distances can be added to your new table and you wont need to change the query any more Happy dayz...

 

[DarkOne72]

Thanks JB! Your a god send! I finally got it all working.
Your idea was a lot better with linking the two tables.

You Rock!!

 

[JBinQLD]

No drama mate, glad you're up and rocking