Query Help

[Alias]

Hello,

I have data (days/times) that i will need to use in a query. I need to
check days of week (M-Sa) against current day of week to make sure it's a
match, if not show a message. I then need to check time of day (05:00-05:00)
against current time to make sure they match too, if not show a message.

Any help on this will be surely appreciated. I have a text field named
DaysHours in table Main with the following type of data:

All days / All hours
M-F / 6:00-6:00
M-F / 5:00-6:00
M-Sa / 8:00-5:00
M-F / 5:00-5:00
M-Sa / 6:00-6:00

(tblMain)
Main_ID
FirstName
LastName
DaysHours
StartDate
EndDate

 

[Beetle]

I'm probably missing something, but I can't figure what it is
you are trying to accomplish. You say you have values like
this in your table;

M-F / 6:00 - 6:00

The above value is neither a date nor a time value. It is just
a string of text.

Then you said you want to take the current weekday (Thursday)
and the current time (1:52 pm) and see if either one of these
match that string?

Please post some more info about what you want to do here
because you will never get a match based on those values.

 

[Alias]

Beetle,

Yea, DaysHours is just a text field. Should I change it to something
different.

So if days/hours for John Doe is "M-F / 6:00-6:00" then if today is Saturday
he can't gain access, or if the current time is before 6:00am or after 6:00pm
he can't gain access.

-A

 

[Scientific]

Beetle,

So what do I need to change to make this work? From your reply I think your
saying I need to change the text field to Date/Time format. If that's the
case, then how would I set up tables, etc. to handle Monday through Saturday
and times like 5:00-5:00, 8:00-5:00. Should I split Days/Hours into two
seperate fields? I don't really know the correct way to approach this...

Thanks for looking at my post.

 

[Beetle]

Apologies as I got busy yesterday and wasn't able to respond. There are
probably a few ways you could do it. One option would be to split this
data up into four separate fields like;

(tblMain)
Main_ID
FirstName
LastName
AllowedFrom (Number)
AllowedTo (Number)
BeginTime (Date/Time)
EndTime (Date/Time)
StartDate
EndDate

In the AllowedFrom/To fields you would store the numeric equivalent
of the days you want to allow access and in code use the Weekday
function to compare.

The Weekday function returns a numeric value for each day of the
week (Sunday=1, Monday=2,.......Saturday=7). Then store the time
values in the other two fields. For the records where you want the
person to have unlimited access (All Days/All Hours) you would store
1/7 in the AllowedFrom/To fields and 12:00:00 AM/11:59:59 PM in
the time fields. So the data in the table might look like;

AllowedFrom AllowedTo BeginTime EndTime
1 7 12:00:00 AM 11:59:59 PM
2 6 6:00:00 AM 6:00:00 PM
2 6 5:00:00 AM 6:00:00 PM
2 7 8:00:00 AM 5:00:00 PM
2 6 5:00:00 AM 5:00:00 PM
2 7 6:00:00 AM 6:00:00 PM

Then you would use code like the following to compare those
values to the current date and time;

If Weekday(Date) >= Me.AllowedFrom And Weekday(Date) <= Me.AllowedTo Then
If Time >= Me.StartTime And Time <= Me.EndTime Then
'some code to allow access
Else
MsgBox "Access Denied AT This Time"
'some code to deny access
Else
MsgBox "Access Denied Today"
'some code to deny access
End If

It will first check if the current weekday falls within the specified
parameters. If so, it will then check if the current time falls within
the specified parameters. If both of these cases are true it will
allow access, otherwise it will deny access.

This is just one possibility. Someone else may jump in with a more
efficient way of doing this, but hopefully it will at least provide some
food for thought.

 

[Alias]

Beetle,

First, my apologies for responding as Scientific. I switched to Scientific
because there seems to be another poster in these forums that also goes by
Alias. My original post as Alias was accidental, intended to post as
Scientific.

Anyway, your code looks awesome. I'm at work right now but can't wait to
get home and try things out. The way you write stuff is so easy to follow
and understand. Can't even begin to tell you how much I appreciate what you
did, the table stuff right down to the code and where to respond. Fantastic
job Beetle, your freakin' awesome, the best.

-A & S

 

[Alias]

Beetle,

Just one last thing. What would the Weekday() function look like?

-A & S

 

[Beetle]

Exactly like it looks in the example. The syntax is;

Weekday(DateValue)

In the example I used the Date() function to retrieve the current
date;

Weekday(Date())

You can find more info on the Weekday function in Access help.

 

[Alias]

Beetle,

Oh, Ok. Now I know there's a built-in Weekday() function. I can actually
see how this is going to work. Genius, absolutely genius. Thanks again
Beetle for everything. I owe you big time

-A & S

 

[Alias]

Beetle,

Sorry I reposted about this because I couldn't find this post until just now.

When I try to enter time values in the BeginTime and EndTime fields from my
combo box I get the following error:

"The value you entered isn't valid for this field"

The data type for both BeginTime and EndTime is (Date/Time) and I formatted
them as Long Time. When I try to enter a time value I get the error above.
So then I created a table (AllowedTimes) and manually put all time values AM
and PM then used tblAllowedTimes as Control Source for BeginTime and EndTime
with the same result. Don't know what to do to fix the problem.

Here's what I have:

(tblMain)
Main_ID
AllowedTimes_ID (FK to tblAllowedTimes)
FirstName
LastName
AllowedFrom (Number)
AllowedTo (Number)
BeginTime (Date/Time formatted as Long Time)
EndTime (Date/Time formatted as Long Time)
StartDate
EndDate

(tblAllowedTimes)
AllowedTimes_ID
Allowed_Times (Date/Time formatted as Long Time)

(cboStartTime)
Control Source = (tblMain, BeginTime)


Thanks for helping out with this Beetle.

-A