By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,183 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Forms: Fill a Field automatically on a form based on a date range

P: n/a
Hi, I have a employee vacation database that has a vacation table that
has the employee name, pay week and date of vacation. I have another
lookup table with pay week code and date range for the week. The user
would like to type in a date in the Date of Vacation field and have the
Pay Week field automatically fill in. My dlookup code is not working.
Here is example of what the tables look like:

Employee: Name
Pay Week
Date of Vacation

PayWeek: PW Date Range
06 06/05/06 - 06/09/06
07 06/12/06 - 06/16/06

I can't get it to work when I enter on the form a date in the Date of
Vacation 06/07/06 and have the form automatically put in 06 in Pay Week
field. Any help would be appreciated. Thanks.

Jun 6 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
We need to see more information. The sql that you are attempting to use
would be helpful as a start..

Jun 6 '06 #2

P: n/a
Are those dates really in a single text field, Date Range? Combined?
Jun 6 '06 #3

P: n/a
Yes, they are a single text field.

w_a_n_n_a_l_l_-@-_s_b_c_g_l_o_b_a_l._n_e_t wrote:
Are those dates really in a single text field, Date Range? Combined?


Jun 7 '06 #4

P: n/a
I haven't wrote any sql yet, don't know where to start.

Ron2006 wrote:
We need to see more information. The sql that you are attempting to use
would be helpful as a start..


Jun 7 '06 #5

P: n/a
The single-field approach to the begin and end dates is a problem. If you
have this option, here's what you should do:

You need to split the date fields out and make them real dates, if you want
to do comparisons between some employee's date and those other dates. Add
two new columns in the table where the dates live. Call them
PeriodBeginDate and PeriodEndDate, or something along that line. If you're
lucky, and all of your dates are formatted as mm/dd/yyyyy - mm/dd/yyyy then
you could use this function to extract them. Then use the extracted dates
to update the new date fields.

Public Function GetDateFromString(strWhich As String, strDateRange As
String) As Variant
'Receive date range string formatted m/d/yyyy-m/d/yyyy and return Begin or
End date, according to value ("B" or "E") of strWhich

Dim varDate As Variant

GetDateFromString = Null
If strDateRange = "" Then
Exit Function
End If
Select Case strWhich
Case "B"
varDate = Trim(Left(strDateRange, InStr(1, strDateRange, "-") -
1))
Case "E"
varDate = Trim(Right(strDateRange, Len(strDateRange) - InStr(1,
strDateRange, "-")))
Case Else
Exit Function
End Select
If IsDate(varDate) = False Then
varDate = Null
End If
GetDateFromString = varDate

End Function

Then you run a query:

Update MyDateRangeTable
Set PeriodBeginDate = GetDateFromString("B", [DateRange]), PeriodEndDate =
GetDateFromString("E", [DateRange])

From that point forward, it would be great to do the data entry into the
date fields directly. You can always concatenate them together for display:

Select [PeriodBeginDate] & " - " & [PeriodEndDate] From MyDateRangeTable
Order By PeriodBeginDate;

Note also that you can sort this now, because you have real dates to work
with.

The point would be to make it possible to select your data using a query
such as this:

Me.txtMyPeriodKey = DLookup("PeriodKey", "MyDateRangeTable",
"PeriodBeginDate <=#" & Me.txtSomeDate & "# And PeriodEndDate >=#" &
Me.txtSomeDate & "#")

I believe that was your ultimate goal, yes?

Any of this helpful?
Jun 7 '06 #6

P: n/a
I keep getting a error message. I have the dates splt, but I keep
getting a syntax error, no matter what I try. I have in the field
where the user enters the date.

Dim varPP As Variant

varPP = DLookup("PP", "PP Lookup", "BeginDate<=" & "me.PP" & "#
and EndDate>=#" & Me.PP & "#")

I can't figure out what is wrong.

Rick Wannall wrote:
The single-field approach to the begin and end dates is a problem. If you
have this option, here's what you should do:

You need to split the date fields out and make them real dates, if you want
to do comparisons between some employee's date and those other dates. Add
two new columns in the table where the dates live. Call them
PeriodBeginDate and PeriodEndDate, or something along that line. If you're
lucky, and all of your dates are formatted as mm/dd/yyyyy - mm/dd/yyyy then
you could use this function to extract them. Then use the extracted dates
to update the new date fields.

Public Function GetDateFromString(strWhich As String, strDateRange As
String) As Variant
'Receive date range string formatted m/d/yyyy-m/d/yyyy and return Begin or
End date, according to value ("B" or "E") of strWhich

Dim varDate As Variant

GetDateFromString = Null
If strDateRange = "" Then
Exit Function
End If
Select Case strWhich
Case "B"
varDate = Trim(Left(strDateRange, InStr(1, strDateRange, "-") -
1))
Case "E"
varDate = Trim(Right(strDateRange, Len(strDateRange) - InStr(1,
strDateRange, "-")))
Case Else
Exit Function
End Select
If IsDate(varDate) = False Then
varDate = Null
End If
GetDateFromString = varDate

End Function

Then you run a query:

Update MyDateRangeTable
Set PeriodBeginDate = GetDateFromString("B", [DateRange]), PeriodEndDate =
GetDateFromString("E", [DateRange])

From that point forward, it would be great to do the data entry into the
date fields directly. You can always concatenate them together for display:

Select [PeriodBeginDate] & " - " & [PeriodEndDate] From MyDateRangeTable
Order By PeriodBeginDate;

Note also that you can sort this now, because you have real dates to work
with.

The point would be to make it possible to select your data using a query
such as this:

Me.txtMyPeriodKey = DLookup("PeriodKey", "MyDateRangeTable",
"PeriodBeginDate <=#" & Me.txtSomeDate & "# And PeriodEndDate >=#" &
Me.txtSomeDate & "#")

I believe that was your ultimate goal, yes?

Any of this helpful?


Jun 28 '06 #7

P: n/a
RoadRunner wrote:
I keep getting a error message. I have the dates splt, but I keep
getting a syntax error, no matter what I try. I have in the field
where the user enters the date.

Dim varPP As Variant

varPP = DLookup("PP", "PP Lookup", "BeginDate<=" & "me.PP" & "#
and EndDate>=#" & Me.PP & "#")

I can't figure out what is wrong.


One thing: You don't have a # in front of me.PP in the first part of the
criteria.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jun 28 '06 #8

P: n/a
I put the # in front of me.PP, but I am still getting a error:
Run-Time Error 3075
Syntax error in date in query expression 'BeginDate<=#me.PP# and
EndDate>=##'

Rick Brandt wrote:
RoadRunner wrote:
I keep getting a error message. I have the dates splt, but I keep
getting a syntax error, no matter what I try. I have in the field
where the user enters the date.

Dim varPP As Variant

varPP = DLookup("PP", "PP Lookup", "BeginDate<=" & "me.PP" & "#
and EndDate>=#" & Me.PP & "#")

I can't figure out what is wrong.


One thing: You don't have a # in front of me.PP in the first part of the
criteria.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


Jun 28 '06 #9

P: n/a
RoadRunner wrote:
I put the # in front of me.PP, but I am still getting a error:
Run-Time Error 3075
Syntax error in date in query expression 'BeginDate<=#me.PP# and
EndDate>=##'


Get rid of the quotes around me.PP. You want to pass the value contained in
me.PP and currently you are passing the literal string "me.PP".
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jun 29 '06 #10

P: n/a
This is what I have typed:

Dim varPP As Variant

varPP = DLookup("PP", "PP Lookup", "BeginDate<=" & #me.PP# & "#
and Enddate>=#" & Me.PP & "#")

and I am still getting a syntax error. I just can't figure out what is
wrong. Thanks for the help in advance.

Jul 13 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.