How do i use AND in a query ?

[jd_boss_hogg]

Hi All - i'm not very good at the basic structuring of queries/sql.... i want to check for 2 conditions, and do some maths depending on the condition. So, in my head, i'd just use an AND/OR condition, but it doesn;t work.

I'll not paste the complicated code because i'm looking for an overview of how i should structure it, basically if A is like "this" then select this stuff, or if A like "that" then select other stuff...

Do i need a 'union' query, and if so, how does that work with the conditions ?

SELECT ([NOTES]*10) AS Chrometime, 
FROM [jb-2001]
WHERE ([NOTES],4) Like "THIS") 

OR

SELECT ([NOTES]*5) AS Chrometime, 
FROM [jb-2001]
WHERE ([NOTES],4) Like "THAT") ;

 

[vbaInet]

Show us some examples of your data and what it should be. An excel spreadsheet will do.

 

[MSAccessRookie]

Hi All - i'm not very good at the basic structuring of queries/sql.... i want to check for 2 conditions, and do some maths depending on the condition. So, in my head, i'd just use an AND/OR condition, but it doesn;t work.

I'll not paste the complicated code because i'm looking for an overview of how i should structure it, basically if A is like "this" then select this stuff, or if A like "that" then select other stuff...

Do i need a 'union' query, and if so, how does that work with the conditions ?

SELECT ([NOTES]*10) AS Chrometime, 
FROM [jb-2001]
WHERE ([NOTES],4) Like "THIS") 
 
OR
 
SELECT ([NOTES]*5) AS Chrometime, 
FROM [jb-2001]
WHERE ([NOTES],4) Like "THAT") ;

I agree with vbaInet that we need to see examples of real data before we can give the help that you are asking for. I assume that the example data was created as an attempt to simplify your problem for explanation. I cannot bsee how it could represent real data, since[NOTES] cannot be both a NUMBER (to calculate Chrometime) and a STRING (containing "THIS" or "THAT"). Please let us know.

 

[jd_boss_hogg]

Hi All, i structured it uisng Iif instead of my Likes (hanks to pat; suggestion), and have it all working lovely know.! Thanks very much for everyones reply...

Here is the final code...

SELECT sum(IIf(Left([jb-2001].NOTES,4) Like "C??H" And [jb-2001].GONE="no",ROUND(((((Val(Mid([NOTES],(InStr(1,[NOTES],"x"))-4,2))*Val(Mid([NOTES],(InStr(1,[NOTES],"x"))-4,2))))*2.2)*[jb-2001].quantity)/60),0)) AS HIGHtime, sum(IIf(Left([jb-2001].NOTES,4) Like "C??S" And [jb-2001].GONE="no",ROUND(((((Val(Mid([NOTES],(InStr(1,[NOTES],"x"))-4,2))*Val(Mid([NOTES],(InStr(1,[NOTES],"x"))-4,2))))*3.3)*[jb-2001].quantity)/60),0)) AS SUPERHIGHtime
FROM [jb-2001];

 

[Brianwarnock]

Isn't he using the ? Wild card

Brian

 

[vbaInet]

Also, you could perform the trim once then re-use that trimmed string in your calculations. An example of a calculation that you could calculate once is
Left([jb-2001].NOTES,4)