How to create a new column in a query?

[SashaBuilder3]

Hi everybody!

I have a table with dates and what I need is to generate a query extracting the date column and creating an additional character column Time_Of_Day ('BN'/'AN'). So if the original table looks like this:

Date_Time Some_value
10/03/2001 08:45 1
10/16/2001 11:10 3
10/26/2001 07:15 5
10/26/2001 18:10 1
11/04/2001 23:55 3
11/10/2001 14:00 4


I need a query to generate something like this:


Date_Time Time_Of_Day Some_value
10/03/2001 18:45 AN 1
10/16/2001 11:10 BN 3
10/26/2001 07:15 BN 5
10/26/2001 18:10 AN 1
11/04/2001 03:55 BN 3
11/10/2001 14:00 AN 4


Can anyone help me write such a query?


Thanks!!!!!

Alexandre

 

[mymou]

Hi SashaBuilder3

Create a query with an extra field as an expression.

SELECT Table.Date_Time, Table.Some_Value,
IIf(Format([Date_Time],"a/p&quot="a","BN","AN&quot AS Time_Of_Day
FROM

;


Stew

 

[SashaBuilder3]

Hi Stew,

Thanks for your response.

I've never encountered such a thing but it works perfect! What is this IIf? I have a few SQL books but I couldn't find (at least in the index) 'IIf'. What topic should I look at to see the description of this? I don't even know how to call it - statement? Function? Sorry for this stupid questions.


Thanks a lot!

Alexandre

 

[mymou]

Hi

This isn't really an SQL thing - but an Acceess thing. This is a 'Built In function' that is available through the expression builder in Access. The function is 'immediate if' I think - and works like any if statement should.

IIF(Logical_Test, Value_If_True, Value_If_False)

Look up 'Examples of expressions' in Access help and see what comes up.

Stew

 

[SashaBuilder3]

Thank you Stew,

I found the function in the Access help. Strange enough that I didn't find the function in my four books on Access programming, nor in my three books on VBA.

Anyway, now I know!

Thanks!

Alexandre