Question Not sure where to post question

[zoso551]

I have a table with a field of information where I need to change the contents as follows:

'emerg' = 010
'urgent' = 020
'routine' = 030
null = 040

One of my challenges is that the table is overwritten (not appended) weekly from an excel spreadsheet.

I made a test query and added field Expr1: Nz([NMWOPriority],'040') which works for the nulls, but I don't know how to get the other words to change.

 

[HGMonaro]

more info required...

are the strings part of the field or the entire field?

if they are the whole field then a simple "update" query will select and replace

if it's part of a field then selecting is easy (use 'like' in the query criteria) but replacing will be harder... maybe use 'instr' & 'len' as a basis for manipulating the string into what you want

 

[zoso551]

More info as requested

Thanks for your response. Bear with me if I don't get all of the Access terminology correct.

More Info: The field in question is the "priority" field. My report has to show the workorders sorted in "priority" order. The order of the priorities is Emerg, Urgent, and Routine, and some priorities are "null". The problem is that if the priority is null, the work order won't show up on my report. I made a table with two columns to "order' the priorities using 010, 020, 030, 040 and joined that to my main table. (The numbers establish the priority order but are "invisible" on the report)

Using the NZ expression in my query, I was able to replace all "nulls" with the value "040" but I am not sure how to get the other priorities to change to a number value.

Ultimately, I have to keep this process as simple as possible for other users, so I need to automate the process as much as possible.

I have made this "forced sort" for another field but there are no "nulls". The nulls are throwing me the curve ball.

 

[HGMonaro]

Yep, your problem is the query won't match the nulls even if you've got a row in your lookup table with a null priority. I don't know of a way to force that to occur so I would run an Update query before your 'order' query to replace the nulls with something else (eg. "Other") that you can add to your 'order' table and ensure they get matched correctly.

So, the full sequence of events would be
1. Add a record to Order table for "Other" with order field = "040"
2. Import Data
3. Run Update query on that table to replace any records with Priority=Null so Priority = "Other" (this would keep fixing all occurances when the table gets overwritten each time it's updated)
4. Run your Join query/report using 'order' to sort.

Hope that helps.

Cheers, Nige