Adding new fields in a query and populate time based on day

[aman]

Hi Guys

I am writing a query that will display data from a table. Along with the fields from the table, I also want to add 2 more fields in a query which are not there in a table and it will check what day is today and if the day is between Monday and Friday then show in new fields say "Daily_From" and "Daily_To" values 8.30am and 8pm.

if its Saturday then store 9am and 2pm.

How this can be done? Any help will be much appreciated.

Thanks

 

[jdraw]

aman,

Fields are in tables.

Show us the table(s) and query.

I find your post is unclear. I suggest you give a couple of examples to clarify what you have and what you want.

 

[aman]

JDraw, there are 2 fields which are not in a table and I don't want to store in a table infact. So i thought of adding 2 expression fields in a query itself and display the values within those based on todays day. If its between Monday to Friday then store values 8.30am and 8pm ..else store 9am and 2pm.

Hope it make sense . thanks

 

[arnelgp]

SELECT Table1.[Name], Table1.DateField, Switch(Format([DateField],"ddd")="Sat","9:00 am",Format([DateField],"ddd")="Sun",Null,True,"8:30 am") AS Daily_From,Switch(Format([DateField],"ddd")="Sat","2:00 pm",Format([DateField],"ddd")="Sun",Null,True,"8:00 pm") AS Daily_To

FROM Table1;

 

[aman]

Arnelgp, here is new field in a query and when i run it then it asks me enter Date value :

Daily_From: Switch(Format([Date],"ddd")="Sat","9:00",Format([Date],"ddd")="Sun",Null,True,"8:30")

 

[aman]

As i don't have any date field so I want to check if today is any day between Monday and Friday then store this value otherwise a different value.

 

[arnelgp]

Use Date() instead of [DATE]

 

[jdraw]

if its between Monday to Friday then store values 8.30am and 8pm ..else store 9am and 2pm.

Store values where?

 

[arnelgp]

He mentioned it in post #1

 

[aman]

Thats great, Many Thanks arnelgp.