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

[Kraig Dalby]

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]

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]

Use the weekday() function. A saturday would be day 7.

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()

 

[Cotswold]

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]

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:

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]

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]

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]

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]

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.