Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

A conditional statements in a query ? 1

Status
Not open for further replies.

robojeff

Technical User
Joined
Dec 5, 2008
Messages
220
Location
US
I need three seperate query condition statements which are similar but slightly different and I am not sure
of the correct syntax for these...

the first condition is as follows:
In a query the field ItemType is a default value of "I" unless the value of StockType = "B" in which case
ItemType = "F"

The second condition is as follows:
In another query the field Commit is a default value of "_" unless the value of SN = "3" or "7" in which case
Commit = "1".

The third condition is as follows:
In a third query, the field Typ is a default value of "_" unless Scode = Yes then Typ = 7 or if Lot = Yes
then Typ = "3"

thanks
 


Chek out the IIF function.

Skip,
[glasses]Don't let the Diatribe...
talk you to death![tongue]

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Thanks for the tip...

I read up on the IIF function and have been able to figure out most of my query conditional
formatting needs but I am still having a problem with the following and could use some advice:

I have a field in a table called "S NR" which is either a "7" or blank.

I would like for SN to reflect the "S NR" field and only populate a "3" in the "S NR" field for any record which contains a number in the Lot Code field

The logic is as such:
If (Lot Code <> Null) Then
SN = "3"
else
SN = [S NR]
End If

I tried to accomplish this with the following:

SN: IIf([Lot Code] Is Not Null,"3", [S NR])

But here are the four types of results that I receive:
result1: "S NR" = 7, Lot Code = blank, SN = 3
result2: "S NR" = blank, Lot Code = blank, SN = 3
result3: "S NR" = blank, Lot Code = blank, SN = blank
result4: "S NR" = blank, Lot Code = 0506P07, SN = 3

In result 1, SN did not get loaded with the value of the "S NR" field

In result 2, SN got loaded with a "3" even though the Lot Code Field was Blank

In result 3 and result 4, SN got loaded correctly.

how can I fix results 1 & 2?

Thanks
 
SN: IIf(Trim([Lot Code] & "")<>"","3",[S NR])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top