Date format in a query field

[R32chaos]

Dear members;

This is not related to a Query necessarily but here is the concern; How can I use a format expression where i can isolate the day from a given date so that it may be represented as "21st" or "22nd" etc...where the "st" and the "nd" can be automatic based on proper english vocabulary.

Your help is very much appreciated, thx in advance.

Regards...Martin

 

[John Big Booty]

The first part is easy just use;

Format(datefield, "d")

or use if you want a single digit days to appear with a leading zero

Format(datefield, "dd")

To get the st, nd etc. I guess you would need to create a lookup table that link the appropriate enumerator to the number returned in one of the above codes, and then append it to your date.

 

[R32chaos]

Thank you very much indeed; the extra table for lookup is a fantastic idea and i am confident it will work great...thx a bunch!!!

One day I hope I can return this favor

 

[jal]

I just tried building a User Defined Function for this. Seems to work okay, needs some testing. I call it "CustomDate" - but you can change this name.

To use it, create a regular module (not a class module) and paste it in. Then you can use it any query. For example, suppose you have a column called OrderDate. You could do a regular select:

SELECT OrderID, CustomDate(OrderDate), CustomerID
FROM Orders




Public Function CustomDate(ByVal Date1 As Date) As String
Dim dayNumber As Integer
dayNumber = DatePart("d", Date1)
Dim suffix As String
Select Case dayNumber
Case 1, 21, 31
suffix = "st"
Case 2, 22
suffix = "nd"
Case 3, 23
suffix = "rd"
Case 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 24, 25, 26, 27, 28, 29, 30
suffix = "th"
End Select
CustomDate = Format(Date1, "MMMM") & " " & dayNumber & suffix & "," & " " & Format(Date1, "YYYY")
End Function

 

[R32chaos]

I just tried building a User Defined Function for this. Seems to work okay, needs some testing. I call it "CustomDate" - but you can change this name.

To use it, create a regular module (not a class module) and paste it in. Then you can use it any query. For example, suppose you have a column called OrderDate. You could do a regular select:

SELECT OrderID, CustomDate(OrderDate), CustomerID
FROM Orders




Public Function CustomDate(ByVal Date1 As Date) As String
Dim dayNumber As Integer
dayNumber = DatePart("d", Date1)
Dim suffix As String
Select Case dayNumber
Case 1, 21, 31
suffix = "st"
Case 2, 22
suffix = "nd"
Case 3, 23
suffix = "rd"
Case 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 24, 25, 26, 27, 28, 29, 30
suffix = "th"
End Select
CustomDate = Format(Date1, "MMMM") & " " & dayNumber & suffix & "," & " " & Format(Date1, "YYYY")
End Function

GREAT
Jal, again THANK YOU!!!
I truly appreciate your help, time, and genuine effort.
"what goes around comes around"

Jal, if there is a study/book for getting a better understanding of VB you can recommend, I will appreciate it even more.

Thank you for pointing me in the right direction.
Regards,
Martin

 

[khawar]

Can be simplified

Public Function CustomDate(ByVal Date1 As Date) As String
Dim dayNumber As Integer
dayNumber = DatePart("d", Date1)
Dim suffix As String
Select Case dayNumber
Case 1, 21, 31
suffix = "st"
Case 2, 22
suffix = "nd"
Case 3, 23
suffix = "rd"
Case Else
suffix = "th"
End Select
CustomDate = Format(Date1, "MMMM") & " " & dayNumber & suffix & "," & " " & Format(Date1, "YYYY")
End Function

 

[R32chaos]

Thank you Khawar!
very much appreciate your input.

I have another question please;
If I am calculating the age in a query "Age: Int((Now()-[DOB])/365)" and wish to include everyone whose birthday is going to be turning 14 a month from now, can i use this criteria: =13.11?
currently the criteria in this "Age" field of the query is =14

Thank you in advance!

 

[raskew]

Here's another function that adds a suffix to any number:

Public Function NumSuffix(mynum As Variant) As String
'*******************************************
'Purpose: Add suffix to a number
'coded by: raskew
'Inputs: ? NumSuffix(234)
'Output: 234th
'*******************************************
Dim n      As Integer
Dim x      As Integer
Dim strSuf As String

    n = Right(mynum, 2)
    x = n Mod 10
    strSuf = Switch(n <> 11 And x = 1, "st", n <> 12 And x = 2, "nd", _
             n <> 13 And x = 3, "rd", True, "th")
    NumSuffix = LTrim(str(mynum)) & strSuf

End Function

Will provide a potential solution to your newly stated age problem in a few minutes.

HTH - Bob

 

[raskew]

Hi -

Here's a function that returns the age based on DOB and an optional date which, if not used converts to the current Date() (just for info, Now() includes both date and time, which probably isn't relevant in an age calculation.

Function fAge2(DOB As Date, Optional dteEnd As Variant) As Integer
're: http://www.access-programmers.co.uk/forums/showthread.php?t=116432
'coded by: raskew
'Inputs:  1) ? fAge2(#4/13/53#, #10/23/06#)
'         2) ? fAge2(#11/1/53#, #10/23/06#)
'         3) ? fage2(#4/13/53#)
'Outputs: 1) 53
'         2) 52
'         3) 53

   dteEnd = IIf(IsMissing(dteEnd), Date, dteEnd)
   fAge2 = DateDiff("yyyy", DOB, dteEnd) + (DateSerial(year(dteEnd), month(DOB), day(DOB)) > dteEnd)

End Function

I'd suggest using this rather than the Int((Now()-[DOB])/365) business, which is somewhat inaccurate.

HTH - Bob

 

[jal]

GREAT

Jal, if there is a study/book for getting a better understanding of VB you can recommend, I will appreciate it even more.

I use Safari books online ($10 per month) because you can copy and paste code snippets into your notes. You can cancel at any month, and renew any month - no obligations.

 

[R32chaos]

Raskew and Jal,

Thank you very much for your input and great help!
I very much appreciate it and will put this information to good use.
Will post a message if I run into any obstacles with the coding.

Jal, thanks for you suggestion I will look into it asap.

Again, THANK YOU!

Regards, Martin