Adding new fields in a query and populate time based on day (1 Viewer)

A

aman

Registered User.

Local time

Today, 01:53

Joined

Oct 16, 2008

Messages

1,250

• Mar 16, 2018

• #1

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

Super Moderator

Staff member

Local time

Today, 04:53

Joined

Jan 23, 2006

Messages

15,379

• Mar 16, 2018

• #2

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.

 

A

aman

Registered User.

Local time

Today, 01:53

Joined

Oct 16, 2008

Messages

1,250

• Mar 16, 2018

• #3

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

..forever waiting... waiting for jellybean!

Local time

Today, 16:53

Joined

May 7, 2009

Messages

19,232

• Mar 16, 2018

• #4

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;

 

A

aman

Registered User.

Local time

Today, 01:53

Joined

Oct 16, 2008

Messages

1,250

• Mar 16, 2018

• #5

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

Code:Copy to clipboard

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

 

A

aman

Registered User.

Local time

Today, 01:53

Joined

Oct 16, 2008

Messages

1,250

• Mar 16, 2018

• #6

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

..forever waiting... waiting for jellybean!

Local time

Today, 16:53

Joined

May 7, 2009

Messages

19,232

• Mar 16, 2018

• #7

Use Date() instead of [DATE]

 

jdraw

Super Moderator

Staff member

Local time

Today, 04:53

Joined

Jan 23, 2006

Messages

15,379

• Mar 16, 2018

• #8

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

Click to expand...

Store values where?

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 16:53

Joined

May 7, 2009

Messages

19,232

• Mar 16, 2018

• #9

He mentioned it in post #1

 

A

aman

Registered User.

Local time

Today, 01:53

Joined

Oct 16, 2008

Messages

1,250

• Mar 16, 2018

• #10

Thats great, Many Thanks arnelgp.