If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
If then statement??
I am new to Access and am trying to take a field and change the value
depending on what the current value is. example If [field 1] is 01 then "supplies", if [field 1] is 02 then "merchandise", if [field 1] is 03 then "equipment", else "blank" can i do this in a query for a field or do i need to do it in a make table query then grab that field from there?? Thanks |
#2
|
|||
|
|||
If then statement??
Try this --
IIF([field 1] = "01", "supplies", IIF([field 1] = "02", "merchandise", IIF([field 1] = "03", "equipment", ""))) -- Build a little, test a little. "Schon" wrote: I am new to Access and am trying to take a field and change the value depending on what the current value is. example If [field 1] is 01 then "supplies", if [field 1] is 02 then "merchandise", if [field 1] is 03 then "equipment", else "blank" can i do this in a query for a field or do i need to do it in a make table query then grab that field from there?? Thanks |
#3
|
|||
|
|||
If then statement??
You can use a calculated field.
Field: IIF([Field 1] ="01","Supplies",IIF([Field 1] ="02","Merchandise", IIF([Field 1] ="03","Equipment",Null))) It would be better to build a small table with the two fields - ItemTypeCode and ItemTypeDescription. Then add this new table your existing query and join ItemTypeCode to the existing field. Double-click on the join line and select the option to give you ALL records in the existing table and only matches from the new table. One BIG advantage to this is that if you add another code to Field 1 "04", then all you need to do is to add one new record to the new table and it will automatically carry through to any existing queries, reports, forms, etc. without you needing to do any additional work. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Schon wrote: I am new to Access and am trying to take a field and change the value depending on what the current value is. example If [field 1] is 01 then "supplies", if [field 1] is 02 then "merchandise", if [field 1] is 03 then "equipment", else "blank" can i do this in a query for a field or do i need to do it in a make table query then grab that field from there?? Thanks |
Thread Tools | |
Display Modes | |
|
|