Date Formatting

[Bunky]

Here is an interesting problem.
I first take a date called and convert it using Format to a Wk of the year,
Month of the year and the year. So a call date of 2/13/2008 equates to
070208. Since I needed to get all the calls made in a specific week, this
sufficed. However, I need to now see if any reservations were made 30 days
or less from the start of the call date. Is there a way I can take 070208
and turn it back into 02/13/08?

Thanks for your assistance!

 

[Wayne-I-M]

Not tested (don't have access on the machine) but something like

SELECT Format(Left([TableName]![YourDateField],2) &
Mid([TableName]![YourDateField],3,2) &
Right([TableName]![YourDateField],2),"Short Date") AS NewDate
FROM TableName;

"should" work ok - you will need to alter the date format dependng on where
you are

 

[Ron2006]

You do not indicate whether you have the original date stored.

If not then the closest you can get is to say that your range should
be 5 weeks prior since 30 days is 4 * 7 plus 2 days into the prior
week and you do not know if the origial date (week) is at the
beginning or the end of the week.

If all you have is the week, month and year you CANNOT figure out
which date it is because 7 different days qualify for that coding.

And also when you set up the range of week/dates you will HAVE to put
the year as the first part of the compare otherwise February 13 of
2007 will fit within the range of Feb 13, 2008 and the code for 5
weeks prior to that.

The only accurate way is to use an actual date.

Ron

 

[John W. Vinson]

Here is an interesting problem.
I first take a date called and convert it using Format to a Wk of the year,
Month of the year and the year. So a call date of 2/13/2008 equates to
070208. Since I needed to get all the calls made in a specific week, this
sufficed. However, I need to now see if any reservations were made 30 days
or less from the start of the call date. Is there a way I can take 070208
and turn it back into 02/13/08?

Thanks for your assistance!

No, because you have discarded and destroyed information in the process. The
week and the month are not sufficient to define the day of the year (there's a
seven day ambiguity).

This is a good example of why you should not confuse data STORAGE with data
DISPLAY. If you had stored the actual call date in your table you could have
easily *displayed* it in your very peculiar week-month-year format, without
losing the day.

You can get an *approximation* of the date back by assuming that all the calls
were made on the first day of the week by recalculating the date from the
formatted value, using an expression like

DateSerial(2000 + Val(Right([yourfield], 2), 1, 7*Val(Left([yourfield], 2)))

You may want to tweak this to handle which day of the week was January 1, or
to incorporate the month, but it'll be pretty complicated!

 

[Bunky]

First of all, I would like to thank everyone who answered; this is the best
group I have ever encountered; Thank you!

I have not lost any data; I am storing both the created date and the actual
call date. Let me explain more fully. I have a form that I am using for the
operator to select, from a pull-down menu, several variables among which is
the BatchID that is made up of the wk, mo, and the year of the call date.
There are several days that pass between the date the data is pulled and the
date the call is made but it is always in a calendar week. What I am doing
is marrying the call made data to the call sent data. Since the call sent
data is only a single day and the call made data can be multiple dates, I
wanted to group these dates so the operator knows just to view a report from
140408 batch id. The query would go find all the data being used and
retrieve it for processing. This works fine. However, I also need to look
to see if a reservation has been made from the date of the first call out 30
days. Without having the operator enter this date, I can only think of
making a table that contains the date called date with the appropriate batch
id and I did not want to make another table. I thought I should be able to
take the batch id (140408) and turn it back into 02/13/08.

Here is an interesting problem.
I first take a date called and convert it using Format to a Wk of the year,
Month of the year and the year. So a call date of 2/13/2008 equates to
070208. Since I needed to get all the calls made in a specific week, this
sufficed. However, I need to now see if any reservations were made 30 days
or less from the start of the call date. Is there a way I can take 070208
and turn it back into 02/13/08?

Thanks for your assistance!

No, because you have discarded and destroyed information in the process. The
week and the month are not sufficient to define the day of the year (there's a
seven day ambiguity).

This is a good example of why you should not confuse data STORAGE with data
DISPLAY. If you had stored the actual call date in your table you could have
easily *displayed* it in your very peculiar week-month-year format, without
losing the day.

You can get an *approximation* of the date back by assuming that all the calls
were made on the first day of the week by recalculating the date from the
formatted value, using an expression like

DateSerial(2000 + Val(Right([yourfield], 2), 1, 7*Val(Left([yourfield], 2)))

You may want to tweak this to handle which day of the week was January 1, or
to incorporate the month, but it'll be pretty complicated!

 

[Ken Sheridan]

You can get one particular day of the week from your formatted value, but not
which particular day. The easiest way to do this s first to create a table
which has two columns , one of your formatted dates over a range of years and
one of the corresponding real date on a chosen day of the week. The
following function is adapted from one of my own and will create such a table
automatically for you. Just pate it into a standard module and call it from
the debug window (aka the immediate window) which you can open by pressing
Ctrl + G. The final parameter array argument determines which days to
incluce in the table, so if you wanted Sundays you'd use 1, Mondays 2 and sp
on. Lets say you want to create a table of Mondays called MondaysCalendar
covering from the first Monday in 2008 to the last in 2017, you'd call it
with:

MakeCalendar_DAO "MondaysCalendar",#01/01/2008#,#12/31/2017#,2

Here's the function:

Public Function MakeCalendar_DAO(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set dbs = CurrentDb

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tdf = dbs.TableDefs(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
dbs.Execute strSQL
Else
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, calWeek CHAR(6)," & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
dbs.Execute strSQL

' refresh database window
Application.RefreshDatabaseWindow

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate, calWeek) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#,"""
& _
Format(Format(dtmDate, "wwmmyy"), "000000") & """)"
dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate, calWeek)
" & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#,"""
& _
Format(Format(dtmDate, "wwmmyy"), "000000") & """)"
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If

End Function

You can then simply look up the Monday date corresponding to your formatted
value with:

DLookup("calDate","MondaysCalendar","calWeek=""" & [CallDate] & """")

where [CallDate] is your formatted call date field, which would give you 11
February 2008, for 070208.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

I thought I should be able to
take the batch id (140408) and turn it back into 02/13/08.

What are the batch ID's for 2/11/08? 2/13/08? 2/15/08? 2/16/08?

If I understand your algorithm they are ALL THE SAME because they are all in
the same week. If all you have is the week, month, and year then you do NOT
have the day.

 

[Bunky]

Yes, it would be all the same batchid. However, I stumbled on another
problem that predicates me using this schema at all. Back to the drawing
board!

Thanks to everyone for their assistance!