need help creating a function

  • Thread starter Thread starter dchristo
  • Start date Start date
D

dchristo

I access a table that contains dates; however they are all in text format
i.e. 20010924; i would like to create a function that I can use in a query to
return the correct values such as

Mid([field1]![Date Received],5,2) & "/" & Right(RTrim([field1]![Date
Received]),2) & "/" & Left([field1]![Date Received],4) but if the field is
blank, return a null value.

Can someone please advise if this is possible and if so how can I accomplish.
 
Assuming 20010924 is supposed to be 24 September, 2001, try:

CDate(Format(Date Received], "0000\-00\-00"))

Of course, that will have problems if [Date Received] is Null. What do you
want to display in that case?
 
dchristo said:
I access a table that contains dates; however they are all in text format
i.e. 20010924; i would like to create a function that I can use in a query
to
return the correct values such as

Mid([field1]![Date Received],5,2) & "/" & Right(RTrim([field1]![Date
Received]),2) & "/" & Left([field1]![Date Received],4) but if the field is
blank, return a null value.

Can someone please advise if this is possible and if so how can I
accomplish.
Click to expand...

Air code:

Public Function CorrectedDate(ByVal dtIn As String) As Variant
If Len(dtIn)= 0 then
CorrectedDate = Null
Exit Function
End If
CorrectedDate = Mid(dtIn, 5, 2) & "/" & _
Right(dtIn, 2) & "/" & _
Left(dtIn, 4)
End Function

To call it from your query:

CorrectedDate([Date Received])
 
You could use the following.

IIF(IsDate(Format([YourField],"@@@@-@@-@@")),CDate(Format([YourField],"@@@@-@@-@@")),Null)

If you KNOW that the field will always be a valid date (never null, never
blank, never an invalid value - 20041932) then you can use the shortened format of
CDate(Format([YourField],"@@@@-@@-@@"))

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
I want it to return a null value or blank - I want to the date to be
formatted as 00/00/0000, I tried the expression below and received the
following error message "Data type mismatch in criteria expression".

Douglas J. Steele said:
Assuming 20010924 is supposed to be 24 September, 2001, try:

CDate(Format(Date Received], "0000\-00\-00"))

Of course, that will have problems if [Date Received] is Null. What do you
want to display in that case?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dchristo said:
I access a table that contains dates; however they are all in text format
i.e. 20010924; i would like to create a function that I can use in a query
to
return the correct values such as

Mid([field1]![Date Received],5,2) & "/" & Right(RTrim([field1]![Date
Received]),2) & "/" & Left([field1]![Date Received],4) but if the field is
blank, return a null value.

Can someone please advise if this is possible and if so how can I
accomplish.
Click to expand...
Click to expand...
 
Thank you for your help, I tried your code but recieved the following error
message "Data type mismatch in criteria expression"

Stuart McCall said:
dchristo said:
I access a table that contains dates; however they are all in text format
i.e. 20010924; i would like to create a function that I can use in a query
to
return the correct values such as

Mid([field1]![Date Received],5,2) & "/" & Right(RTrim([field1]![Date
Received]),2) & "/" & Left([field1]![Date Received],4) but if the field is
blank, return a null value.

Can someone please advise if this is possible and if so how can I
accomplish.
Click to expand...

Air code:

Public Function CorrectedDate(ByVal dtIn As String) As Variant
If Len(dtIn)= 0 then
CorrectedDate = Null
Exit Function
End If
CorrectedDate = Mid(dtIn, 5, 2) & "/" & _
Right(dtIn, 2) & "/" & _
Left(dtIn, 4)
End Function

To call it from your query:

CorrectedDate([Date Received])
Click to expand...
 
Thanks for the response, I tried your expression however it did not return
any records

John Spencer said:
You could use the following.

IIF(IsDate(Format([YourField],"@@@@-@@-@@")),CDate(Format([YourField],"@@@@-@@-@@")),Null)

If you KNOW that the field will always be a valid date (never null, never
blank, never an invalid value - 20041932) then you can use the shortened format of
CDate(Format([YourField],"@@@@-@@-@@"))

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I access a table that contains dates; however they are all in text format
i.e. 20010924; i would like to create a function that I can use in a query to
return the correct values such as

Mid([field1]![Date Received],5,2) & "/" & Right(RTrim([field1]![Date
Received]),2) & "/" & Left([field1]![Date Received],4) but if the field is
blank, return a null value.

Can someone please advise if this is possible and if so how can I accomplish.
Click to expand...
Click to expand...
 
To get 00/00/0000 for Null dates, use

IIf(IsNull([Date Received], "00/00/0000", CDate(Format(Date Received],
"0000\-00\-00")))

If you're using this in a query, the expectation is that you're putting this
as a computed field. The fact that your error message mentions "criteria
expression" makes me suspect that you're not using the expression correctly.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dchristo said:
I want it to return a null value or blank - I want to the date to be
formatted as 00/00/0000, I tried the expression below and received the
following error message "Data type mismatch in criteria expression".

Douglas J. Steele said:
Assuming 20010924 is supposed to be 24 September, 2001, try:

CDate(Format(Date Received], "0000\-00\-00"))

Of course, that will have problems if [Date Received] is Null. What do
you
want to display in that case?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dchristo said:
I access a table that contains dates; however they are all in text
format
i.e. 20010924; i would like to create a function that I can use in a
query
to
return the correct values such as

Mid([field1]![Date Received],5,2) & "/" & Right(RTrim([field1]![Date
Received]),2) & "/" & Left([field1]![Date Received],4) but if the field
is
blank, return a null value.

Can someone please advise if this is possible and if so how can I
accomplish.
Click to expand...
Click to expand...
Click to expand...
 
After tweaking it abit, I got it to work

Public Function ConvDate(ByVal dtIn As Variant) As Variant

dtIn = RTrim(dtIn)

If Len(dtIn) = 0 Or IsNull(dtIn) Then
ConvDate = Null
Exit Function
End If
ConvDate = Mid(dtIn, 5, 2) & "/" & Mid(dtIn, 7, 2) & "/" & Left(dtIn, 4)
End Function


Stuart McCall said:
dchristo said:
I access a table that contains dates; however they are all in text format
i.e. 20010924; i would like to create a function that I can use in a query
to
return the correct values such as

Mid([field1]![Date Received],5,2) & "/" & Right(RTrim([field1]![Date
Received]),2) & "/" & Left([field1]![Date Received],4) but if the field is
blank, return a null value.

Can someone please advise if this is possible and if so how can I
accomplish.
Click to expand...

Air code:

Public Function CorrectedDate(ByVal dtIn As String) As Variant
If Len(dtIn)= 0 then
CorrectedDate = Null
Exit Function
End If
CorrectedDate = Mid(dtIn, 5, 2) & "/" & _
Right(dtIn, 2) & "/" & _
Left(dtIn, 4)
End Function

To call it from your query:

CorrectedDate([Date Received])
Click to expand...
 
I am sorry, what do you mean "it did not return any records"? The expression
was a calculated field to be used as a field. It is not criteria to identify
records.

Do you mean is did not return any values other than Null? If so, I would
suspect that you have not told us everything about the field in question. For
instance, does the source field have more than 8 characters in it? Or does the
field have leading spaces?

I just retested the expression and got the expected results
Set variable to a string in the format you specified
YourField="20080131"
Use the value in the expression and print the result

?IIF(IsDate(Format(YourField,"@@@@-@@-@@")),CDate(Format(YourField,"@@@@-@@-@@")),Null)
The result was
1/31/2008

If I pass a string that does not conform to the specification (or null) then I
get an error when I use IIF function in VBA, but I will get NULL in a query.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Thanks for the response, I tried your expression however it did not return
any records

John Spencer said:
You could use the following.

IIF(IsDate(Format([YourField],"@@@@-@@-@@")),CDate(Format([YourField],"@@@@-@@-@@")),Null)

If you KNOW that the field will always be a valid date (never null, never
blank, never an invalid value - 20041932) then you can use the shortened format of
CDate(Format([YourField],"@@@@-@@-@@"))

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I access a table that contains dates; however they are all in text format
i.e. 20010924; i would like to create a function that I can use in a query to
return the correct values such as

Mid([field1]![Date Received],5,2) & "/" & Right(RTrim([field1]![Date
Received]),2) & "/" & Left([field1]![Date Received],4) but if the field is
blank, return a null value.

Can someone please advise if this is possible and if so how can I accomplish.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top