Is it possible to create a date validation rule for "date must be a saturday" and other date validation rules questions? (1 Viewer)

Kraig Dalby

New member
Local time
Today, 14:43
Joined
Jul 6, 2021
Messages
2
I'm pretty new to this and I might be doing it all wrong as I try to work though it but I was hoping I can get some advice over here as my searches a proving fruitless.

I need to set a field validation rule so that an entry can only be created with a date which is a Saturday. I was hoping to use a rule rather than having to create a look up table, is this possible?

I also need to know how to auto fill dates into other tables (via a form, or a form nested in a form i'm still working it all out) based on the original date selection. I would guess thats something along the lines of "date field" + x days

Is there a good guide on how to write these rules floating around, I would prefer to understand what I'm doing rather than just copy pasting something in?

Thanks
 

mike60smart

Registered User.
Local time
Today, 14:43
Joined
Aug 6, 2017
Messages
1,899
I'm pretty new to this and I might be doing it all wrong as I try to work though it but I was hoping I can get some advice over here as my searches a proving fruitless.

I need to set a field validation rule so that an entry can only be created with a date which is a Saturday. I was hoping to use a rule rather than having to create a look up table, is this possible?

I also need to know how to auto fill dates into other tables (via a form, or a form nested in a form i'm still working it all out) based on the original date selection. I would guess thats something along the lines of "date field" + x days

Is there a good guide on how to write these rules floating around, I would prefer to understand what I'm doing rather than just copy pasting something in?

Thanks
Hi Kraig
Welcome to the Forum.

It would help if you can explain in more detail the Step by Step process of doing data entry for a Record.
 

moke123

AWF VIP
Local time
Today, 10:43
Joined
Jan 11, 2013
Messages
3,852
Use the weekday() function. A saturday would be day 7.

Code:
If WeekDay(YourDate)<> 7 then
"msgbox "It ain't a Saturday"
End if

I would guess thats something along the lines of "date field" + x days
Check out the DateAdd()
 
Last edited:

Cotswold

Active member
Local time
Today, 14:43
Joined
Dec 31, 2020
Messages
521
Hi Kraig,
You need to look up and understand the explanations in the Language Reference for the various Date Functions and Statements
Date, Date$, DateAdd(), DateDiff(), DatePart(), DateSerial() and DateValue(). Also look at the WeekDay() Function, as well as Day() and the other related time and date Functions, Hour(), Minute(), Month(), Year(), Second() and Now()

In particular look at DatePart() which allows you to set the FirstWeekday and the FirstWeek (of the year)
Unless set otherwise Access will default to Sunday as the FirstWeekDay, with the value 1. So Saturday will be 7.

You can also arrive at the WeekNumber, (week number in a year) which can be useful at times. If you do build week numbers in then make sure to get Week One in a year correct, including for Leap Years.

Also, be aware that the date settings in Windows, Short Date settings, etc., can affect your results. You can see these from RightClicking the date in the right of system tray. An incorrectly set PC will mess up your calcs. So if things are going wrong that shouldn't, it is something to check.

Using the above Functions you can then write your own Function in Modules to return the values required.

Regards, Will
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:43
Joined
May 7, 2009
Messages
19,175
you can make the Date textbox to be the first control in your form.
Add code to your date textbox/combobox BeforeUpdatet event to validate:

Code:
Private Sub DateTextbox_BeforeUpdate(Cancel As Integer)
    If IsDate(Me!DateTextbox) Then
        If Format$(Me!DateTextbox, "ddd") <> "Sat" Then
            Cancel = True
            MsgBox "You need to enter a date that is 'saturday' as day of the week!"
        End If
    Else
        Cancel = True
        MsgBox "Please enter a valid date."
    End If
End Sub
 

Kraig Dalby

New member
Local time
Today, 14:43
Joined
Jul 6, 2021
Messages
2
Thanks Mike

Ok... so this is all about tracking hours worked, holiday ect for my teams. I'm largely trying to approach this from a "make a useable form for my staff to input data" persective.

The main table is pretty basic with the fields; ID, Date, Name, Hours, Status (worked, sick, holiday ect ect).

The way I understand queries from that I can do things like find average hours worked a week between a range of dates or find total hours taken as holiday in a period. But i'll worry about how to do that later.

I want a form my staff can use to enter the data but I am trying to cut down on repeating opperations and minimizing user error. I need them to enter a weeks worth of data at a time.

So Im going to try and explain what I imagin I want

1. On the form I was hoping to have a "weekending" input (this needs to be a saturday).

then each line from the user persective (produced as a list here for ease of reading) would have the following

2. Name - drop down menu, select staff member from a staff lookup table

(sub form / nested form starts - "Main table") seven input sections labled sunday-monday (from the users perspective anyway) but only one given here as an example)

3. Field "name" hidden from the users view but auto entered from "2"
4. Field "date" hidden from user view but auto generated from "1" - the number of days neccesary to get the right date
5. Hours (data input by user)
6. Status (data input from user)

Repeat from "3" for each day of the week.

New line starting from "2" again

Lastly a confirm button so all records can be created at one time from the filled in form.

Make sense? Possible?

Thanks

Kraig
 

mike60smart

Registered User.
Local time
Today, 14:43
Joined
Aug 6, 2017
Messages
1,899
Hi Kraig
So you have Supervisors in Charge of Teams.

I would imagine that trying to automate a weeks worth of activity for a Number of Members of a Team would be quite difficult.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:43
Joined
Feb 19, 2002
Messages
42,984
This will be much simpler for you if you give up trying to make it look like a spreadsheet. Store the time entries in a normalized manor. One date/task per row. You can use the Saturday date in an unbound control to create a value list for the subform so the user can pick by day of the week, and the actual date will be stored.

The form will be a main form based on the employee table and a subform to enter the details. There will be an unbound combo on the main form to select the employee.

I don't normally recommend the MS templates because I don't like them but install the TimeCard template and look at the tables. They will give you an idea of what a normalized schema looks like.

Do NOT use embedded spaces or special characters in ANY object names though. This is one of my pet peeves with the templates.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:43
Joined
Sep 12, 2006
Messages
15,614
I doubt you can force a Saturday at the table level, but it's easy to write validation code.
As an alternative, you could just store the week number corresponding to the Saturday - although then you have to synchronise the code that drives the calendar, to identify which Saturday falls in week 1. But then you could have a unique index on Year+WeekNumber, and you could easily limit the database to a single entry for each week. It depends how you need to control the data.
 

Users who are viewing this thread

Top Bottom