0

I am new to MS Access VBA.. I have imported the log file to the table and would like to extract to another table. 1) all logs/records where DATE_Field = Weekend. 2) all logs/records where DATE_Field is weekday AND Time_Field between 22:00 and 05:00

I tried Query and tried to convert the date field using "datename" but error says there is no valid function with that name

DoW: datename([LogFile].Date)

I would like to extract all logs which are 1) all logs/records where DATE_Field = Weekend. 2) all logs/records where DATE_Field is weekday AND Time_Field between 22:00 and 05:00

Alex
  • 878
  • 1
  • 10
  • 25
Oki Toma
  • 15
  • 8

2 Answers2

1

Apparently access has a weekday() function:

https://support.office.com/en-gb/article/weekday-function-05d360d7-2c3f-4691-9448-c96ea0351940

I recommend you use the parameter that fixes the start of the week to avoid regional variations affecting your query

WHERE weekday(datefield, 1) IN (1,7)

Your weekday query would be similar; use NOT IN, combined with a test that your time field is <= '05:00' OR >= '22:00', assuming time is a string. It'll need a leading zero to work out correctly. Remember to use brackets when using OR.

WHERE ... NOT IN (1,7) ANd (timefield <= '05:00' OR timefield >= '22:00')
Oki Toma
  • 15
  • 8
Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • Thank you for quick help. I used the below query and its returning. "vbSunday" was not working in the ACCESS Query creater so used number values. – Oki Toma May 22 '19 at 06:06
  • Can you help me with this question? I tried everything I knew but could not solve. https://stackoverflow.com/q/56207459/8631622 @Caius Jard – Pie May 22 '19 at 07:11
0

The first you can get by filtering on the weekday where you set the second argument to 2 for vbMonday:

Select * From YourTable
Where Weekday([DATE_Field], 2) > 5

For the second task, use an expanded criteria:

Select * From YourTable
Where 
    Weekday([DATE_Field], 2) <= 5
    And
    [TIME_Field] Not Between #05:00:00# And #22:00:00#

Date/time values in Access are wrapped in octothorpes (#).

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Thank you.. I have used this and looks working correctly for me .....WHERE ((([03_WorkFile].年月日) Not In ([00_Holiday].[Date-Holiday])) AND (([03_WorkFile].時間)>#12/30/1899 22:0:0# Or ([03_WorkFile].時間)<#12/30/1899 5:0:0#) AND ((Weekday([年月日],1)) Not In (1,7))) – Oki Toma May 23 '19 at 08:34
  • Yes, a little more verbose, but will return the same result. – Gustav May 23 '19 at 08:47