Handling Date with DAY

[David44Coder]

Can't seem to find anything on this.. and various combinations go to error e.g.

Dim df as String, Cd as Date
df = "Saturday, 19 Mar 1983"
cd = DateValue(df)

Whether Sat or Saturday is used, same error. CDate is no better.
What would be the correct way to cast a string date including Day to a Date variable?

 

[June7]

Have to use string manipulation to parse the string. If day is always followed by comma and space:

cd = CDate(Mid(strDate, InStr(strDate, ",") + 1))

 

[The_Doc_Man]

Saturday isn't technically part of the date. It is a conveniently derived property of the week, whereas the other date elements are parts of a year. I.e. the week is a derived, not an intrinsic quantity. Therefore it should not be in the input string. It specifies a part of something that is only indirectly part of a date.

For conversions using standard Access routines, you don't want to include anything that isn't necessary for those standard routines to do their work. Access date/time routines don't deal with any unit smaller than seconds. Which is why you cannot use fractions of a second such as 23:14:56.78 as a time and why you cannot include the day of the week. The date/time routines don't want them there.

EDIT: June7 beat me to it.

You have to parse out the day of the week. But June7's suggestion might be done easier with the SPLIT function and one more variable. Here is a link to the SPLIT function.

Split function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

Here is how I might have approached the problem as you wrote it.

Dim DF as String, DV as variant, CD as Date
DF = "Saturday, 19 Mar 1983"
DV = SPLIT( DF, ",", -1, 1 )
CD = DateValue( DV(1) )

In this case, the -1 (3rd argument) means "return as many split elements as there are" to the variant DV. The 1 (4th argument) is a substitute for the constant that vbTextCompare would translate to if you had the right library loaded. And I used DV(1) because SPLIT returns a zero-based array, which means that in this case, DV(0) would contain "Saturday" (and no comma).

 

[arnelgp]

another variation:

Dim df as String, Cd as Date
df = "Saturday, 19 Mar 1983"
cd = CDate(Mid(df,Instr(1,df," ")))

 

[Gasman]

Why do you need the day?
Always worth looking at the MS docs for a function etc?

DATEVALUE function - Microsoft Support

The DATEVALUE function converts a date that is stored as text to a serial number that Excel recognizes as a date. The DATEVALUE function is helpful in cases where a worksheet contains dates in a text format that you want to filter, sort, or format as dates, or use in date calculations.

support.microsoft.com

 

[June7]

DateValue() cannot directly convert this string. The day part must be removed first then DateValue or CDate can convert the string date to a number date.

The expression I suggested could be calculated in query or textbox without VBA. Reference field in place of strDate variable.