SQL query for VBA - MSAccess (1 Viewer)

A

asamid

New member

Local time

Today, 15:26

Joined

May 8, 2017

Messages

3

• May 8, 2017

• #1

Hi there!
I have a table that has more than one date fields (say six or seven) and i want to retrieve only the dates that match today's date. The result should hold the fields such as, the first two columns (project id and project title) and along with the date columns (only those that have today's date; not all other dates required in the result). Actually this result should serve as a report for the user. in other words it should be a todo list for today. Thanks for your help. appreciate it

 

C

CJ_London

Super Moderator

Staff member

Local time

Today, 23:26

Joined

Feb 19, 2013

Messages

16,610

• May 8, 2017

• #2

sounds like your table is not normalised so you will need to use a number of OR's in your query

SELECT *
FROM myTable
WHERE Date1=Date() OR Date2=Date() OR....

You will then need to have some code to modify your form or report to hide those controls which do not have todays date in them and possible reposition the ones that do.

 

A

asamid

New member

Local time

Today, 15:26

Joined

May 8, 2017

Messages

3

• May 8, 2017

• #3

HI CJ
Thanks for your advice. i will first give it a try. However i was just thinking the form or query should automatically show/hide the dates matching today/not matching. shouldn't be dynamic? Please correct me if i am wrong.

Plus, how do i dynamically hide the controls that should have the dates other than today's? hope you don't mind.
Thanks a lot.

 

C

CJ_London

Super Moderator

Staff member

Local time

Today, 23:26

Joined

Feb 19, 2013

Messages

16,610

• May 8, 2017

• #4

However i was just thinking the form or query should automatically show/hide the dates matching today/not matching. shouldn't be dynamic?

Click to expand...

No - as you've described it you have a number of date fields in a record. if any of those date fields are today, then all of them will appear for that record. What if the first record has todays date in the first field, and the second record todays date in the third field?

Regret I cannot help with hiding fields code. If your data was normalised, you would not need any code.

 

A

asamid

New member

Local time

Today, 15:26

Joined

May 8, 2017

Messages

3

• May 8, 2017

• #5

Thanks a lot CJ
I'll see what i can do to hide those non-today's date columns.
appreciate it