How to Concatenate Numbers in a function and create a date

  • Thread starter Thread starter Desilu via AccessMonster.com
  • Start date Start date
D

Desilu via AccessMonster.com

I am working with an Access front-end where the table is coming from a Sequel
DB. I want to create a function in a module to create a date, based on the
Month, Day, Century and Year fields. I want the FINAL date in a MM/DD/YYYY
format. For the fields that are null or equal to zero, I need an IF, Then,
Else statement to convert those concatenated values to the date #1/1/1900#.
I'm close, but not close enough, as I'm still learning. Any help is
appeciated!

The table fields that I'm working with have been brought over to the Access
front-end as numbers from 1 to 2 positions in field length. Below are the
fields:

Month = 2
Day = 15
Century Year = 20
Year = 1

Date would be 2/15/2001

Thanks for your help.
 
I am working with an Access front-end where the table is coming from a Sequel
DB. I want to create a function in a module to create a date, based on the
Month, Day, Century and Year fields. I want the FINAL date in a MM/DD/YYYY
format. For the fields that are null or equal to zero, I need an IF, Then,
Else statement to convert those concatenated values to the date #1/1/1900#.
I'm close, but not close enough, as I'm still learning. Any help is
appeciated!

The table fields that I'm working with have been brought over to the Access
front-end as numbers from 1 to 2 positions in field length. Below are the
fields:

Month = 2
Day = 15
Century Year = 20
Year = 1

Date would be 2/15/2001

Thanks for your help.
Click to expand...

You don't need a function in a module. You can do this directly in the
control source of an unbound text control on a form or in a report.

Assuming the field datatypes are all Number,

=IIf(IsNull([Year]) Or IsNull([Month]) or
IsNull([Day]),#1/1/1900#,DateSerial(2000 + [Year],[Month],[Day]))

should create a date value out of that data.
You can set the Format property of this control to
mm/dd/yyyy
to display in mm/dd/yyyy format.

Note: Year, Month, and Day are all Access/VBA reserved words and
should not be used as field names. Change those field names, i.e. to
AYear, AMonth, and ADay, for example.

For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html
 
I realize it doesn't have to be a function in a module and that it may be
accomplished elsewhere, however I'm dealing with users who will be querying
against these tables and they do not know how to manipulate data on their own.
Therefore, to keep it less difficult my solution was to create a function in
a module, for myself as well as them, that we could use over an over again in
our queries.

Below is what I had, but I do not know how to nest the IF statement within to
evaluate the 0 or Null values. I also realize that it may not need to be
Public.

Public Function ConvertDate(MMonth, DDay, CCentury, YYear) As Date

Dim MyValueMonth
MyValueMonth = Val(MMonth)

Dim MyValueDay
MyValueDay = Val(DDay)

Dim MyValueCentury
MyValueCentury = Val(CCentury) * 100

Dim MyValueYear
MyValueYear = Val(YYear)


ConvertDate = (MyValueMonth) & "/" & (MyValueDay) & "/" & ((MyValueCentury) +
(MyValueYear))

End Function

Thanks for any help!!!
I'm desperate.
Desilu
I am working with an Access front-end where the table is coming from a Sequel
DB. I want to create a function in a module to create a date, based on the
Click to expand...
[quoted text clipped - 16 lines]
Thanks for your help.
Click to expand...

You don't need a function in a module. You can do this directly in the
control source of an unbound text control on a form or in a report.

Assuming the field datatypes are all Number,

=IIf(IsNull([Year]) Or IsNull([Month]) or
IsNull([Day]),#1/1/1900#,DateSerial(2000 + [Year],[Month],[Day]))

should create a date value out of that data.
You can set the Format property of this control to
mm/dd/yyyy
to display in mm/dd/yyyy format.

Note: Year, Month, and Day are all Access/VBA reserved words and
should not be used as field names. Change those field names, i.e. to
AYear, AMonth, and ADay, for example.

For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html
Click to expand...
 
Public Function ConvertDate(MMonth as variant, DDay as variant, CCentury as
variant, YYear as variant) As Date

ConvertDate = #1/1/1900#
If MMonth >=1 and <=12 Then
If DDay >= 1 and <= 31 Then
If CCentury >= 19 and <= 20 Then
If YYear >= 0 and <= 99 Then
If IsDate((MMonth) & "/" & (DDay) & "/" & ((CCentury * 100)
+ (YYear)) then
ConvertDate = cdate((MMonth) & "/" & (DDay) & "/" &
((CCentury * 100) + (YYear))
Else
End If
Else
End If
Else
End If
Else
End If
Else
End If

End Function

I added the IsDate() because the individual field edits do not ensure a
valid date and this will trap values that are out of range when combined
with other values. Wouldn't it just be easier to store the date properly in
the table?


Desilu via AccessMonster.com said:
I realize it doesn't have to be a function in a module and that it may be
accomplished elsewhere, however I'm dealing with users who will be
querying
against these tables and they do not know how to manipulate data on their
own.
Therefore, to keep it less difficult my solution was to create a function
in
a module, for myself as well as them, that we could use over an over again
in
our queries.

Below is what I had, but I do not know how to nest the IF statement within
to
evaluate the 0 or Null values. I also realize that it may not need to be
Public.

Public Function ConvertDate(MMonth, DDay, CCentury, YYear) As Date

Dim MyValueMonth
MyValueMonth = Val(MMonth)

Dim MyValueDay
MyValueDay = Val(DDay)

Dim MyValueCentury
MyValueCentury = Val(CCentury) * 100

Dim MyValueYear
MyValueYear = Val(YYear)


ConvertDate = (MyValueMonth) & "/" & (MyValueDay) & "/" &
((MyValueCentury) +
(MyValueYear))

End Function

Thanks for any help!!!
I'm desperate.
Desilu
I am working with an Access front-end where the table is coming from a
Sequel
DB. I want to create a function in a module to create a date, based on
the
Click to expand...
[quoted text clipped - 16 lines]
Thanks for your help.
Click to expand...

You don't need a function in a module. You can do this directly in the
control source of an unbound text control on a form or in a report.

Assuming the field datatypes are all Number,

=IIf(IsNull([Year]) Or IsNull([Month]) or
IsNull([Day]),#1/1/1900#,DateSerial(2000 + [Year],[Month],[Day]))

should create a date value out of that data.
You can set the Format property of this control to
mm/dd/yyyy
to display in mm/dd/yyyy format.

Note: Year, Month, and Day are all Access/VBA reserved words and
should not be used as field names. Change those field names, i.e. to
AYear, AMonth, and ADay, for example.

For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html
Click to expand...
Click to expand...
 
Yes I agree that it would be easier to store them as dates. This isn't a DB
that I designed, rather a system our company purchased. The tables are being
made available to us through an Access front-end (via an Sequel DB). I'm not
sure how the dates get populated inaccurately, however I have to "deal" with
them on the back-end. Thanks for your help!

Pat said:
Public Function ConvertDate(MMonth as variant, DDay as variant, CCentury as
variant, YYear as variant) As Date

ConvertDate = #1/1/1900#
If MMonth >=1 and <=12 Then
If DDay >= 1 and <= 31 Then
If CCentury >= 19 and <= 20 Then
If YYear >= 0 and <= 99 Then
If IsDate((MMonth) & "/" & (DDay) & "/" & ((CCentury * 100)
+ (YYear)) then
ConvertDate = cdate((MMonth) & "/" & (DDay) & "/" &
((CCentury * 100) + (YYear))
Else
End If
Else
End If
Else
End If
Else
End If
Else
End If

End Function

I added the IsDate() because the individual field edits do not ensure a
valid date and this will trap values that are out of range when combined
with other values. Wouldn't it just be easier to store the date properly in
the table?
I realize it doesn't have to be a function in a module and that it may be
accomplished elsewhere, however I'm dealing with users who will be
Click to expand...
[quoted text clipped - 71 lines]
Click to expand...
 
Back
Top