Medium Date format for DLookup

[NT100]

Hi,

I've a listbox (lstTAssignDt) to hold a number of date and when a date is selected. It will be assigned to dTeach_PIP (type Date).

...
For Each varItm In lstTAssignDt.ItemsSelected
dTeach_PIP = Format(lstTAssignDt.ItemData(varItm), "dd\-mmm\-yyyy")
Next varItm
...


However, when I do a DLookup with this dTeach_PIP and the rest parameters, I go the display as at the immediate windows

iSession = DLookup("[Session]", "tblTeachTT_PIP", "[AcademicYr] = " & iApptYr & " AND [PIPYr] = " & iPIPYr & " AND [dtTeach] = #" & dTeach_PIP & "#")


Immediate windows
? dTeach_PIP
12:00:00 AM

I expected a medium date, e.g. 27-09-2017, is received but it turns up a time. What do you suggest to do for a medium date.

Thank you.

NT100

 

[arnelgp]

format only applies to the display
not how it is saved in the table.

the actual working format is in
English(US): mm/dd/yyyy:

For each varItm In lstTAssingDt.ItemSelected
dTeach_PIP = "#" & format(ltsTAssignDt.Itemdata(varItm), "mm/dd/yyyy") & "#"
Next varItm

iSession = Dlookup("Session","tblTeachTT_PIP", "[AcademicYr]=" & iAppYr & " And [PIPYr]=" & IPIPYr & " And [tdTeach]=" & dtTeach_PIP)

 

[NT100]

I know it's US date format. Can I convert it to medium date format of "dd mmm yyyy"?

 

[Minty]

Yes - but only for display purposes.
You can't use a medium date string for the criteria on the DLookup. It has to have a date, either #mm/dd/yyyy# or #yyyy/mm/dd#

 

[NT100]

I got the same error of

Immediate windows
? dTeach_PIP
12:00:00 AM

 

[Minty]

Show us the whole code please - from variable declaration to the part that errors. We're not seeing the whole picture.

 

[NT100]

I wish to have medium date of dd-mmm-yyyy format for DLookup function.

Here you're the codes.


tables
tblTAssign_PIP
tblTeachTT_PIP


--------------------------------------------------
Option explicit
Private iSession as integer



Private Sub lstPIPYr_Click()

Dim dTeach_PIP As Date
Dim sSQLTAssignDt_PIPYr As String
Dim rsTAssignDt_PIPYr As Recordset
Dim varItm As Variant


' Reset controls
Me.lstTAssignDt = ""
Me.lstTAssignDt.RowSource = ""
Me.lstTAssignDt.RowSourceType = "Value List"

'----------------------
' Get selected PIPYr
'----------------------
lstPIPYr.BoundColumn = 1

For Each varItm In lstPIPYr.ItemsSelected
iPIPYr = lstPIPYr.ItemData(varItm)
Next varItm

'----------------------------------------------
' Get assigned dates of the selected PIPYr
'----------------------------------------------
sSQLTAssignDt_PIPYr = "SELECT * FROM tblTAssign_PIP Where TRef = " & iID & " AND AcademicYr = " & iApptYr & " AND PIPYr = " & iPIPYr

Set rsTAssignDt_PIPYr = db.OpenRecordset(sSQLTAssignDt_PIPYr, dbOpenDynaset, dbSeeChanges)

Me.lstTAssignDt = ""
If rsTAssignDt_PIPYr.RecordCount > 0 Then

rsTAssignDt_PIPYr.MoveFirst

' Assign the dates to lstTAssignDt
Do
iSession = rsTAssignDt_PIPYr!Session
dTeach_PIP = DLookup("[DtTeach]", "tblTeachTT_PIP", "[AcademicYr] = " & iApptYr & " AND [PIPYr] = " & iPIPYr & " AND [Session] = " & iSession)

lstTAssignDt.AddItem Format(dTeach_PIP, "dd\-mmm\-yyyy")

rsTAssignDt_PIPYr.MoveNext

Loop Until rsTAssignDt_PIPYr.EOF
End If

db.Close


Set rsTAssignDt_PIPYr = Nothing
Set db = Nothing


End Sub


=======================================================

Private Sub lstTAssignDt_Click()


Dim dTeach_PIP As Date
Dim varItm As Variant


' Reset controls
Me.txtSCSession = ""


For Each varItm In lstTAssignDt.ItemsSelected
dTeach_PIP = "#" & Format(lstTAssignDt.ItemData(varItm), "mm/dd/yyyy") & "#"
Next varItm


'---------------------------------------------------------------
' Process to get Session from tblTeachTT_PIP
'---------------------------------------------------------------
iSession = DLookup("[Session]", "tblTeachTT_PIP", "[AcademicYr] = " & iApptYr & " AND [PIPYr] = " & iPIPYr & " AND [dtTeach] = " & dTeach_PIP)


'---------------------------------------------------------------
' Get number of student for each session of the selected dtTeach
'---------------------------------------------------------------
txtSCSession.Value = DLookup("[SPerSession]", "tblTAssign_PIP", "[AcademicYr] = " & iApptYr & " AND [PIPYr] = " & iPIPYr & " AND [Session] = " & iSession)


End Sub

 

[Minty]

I'm puzzled why you are looping through the list box items when you are clearly only using the selected one. I meant to ask why earlier.

I suspect this is why you are getting strange results. In this part of the code add the line in red;

For Each varItm In lstTAssignDt.ItemsSelected
[COLOR="Red"]Debug.Print lstTAssignDt.ItemData(varItm)[/COLOR]
dTeach_PIP = "#" & Format(lstTAssignDt.ItemData(varItm), "mm/dd/yyyy") & "#"
Next varItm

And tell us what you get as a result in the immediate window. (Ctrl+G in the vba editor)

 

[NT100]

My intention is to show the data (typed "Date") in forms/reports as "dd-mmm-yyyy". Do I need to set the date as "medium date" format in its table structure for this purpose?

If not, how do I make the date displayed as "dd-mmm-yyyy" in forms/reports ?

Thanks

 

[NT100]

I'm puzzled why you are looping through the list box items when you are clearly only using the selected one. I meant to ask why earlier.

I suspect this is why you are getting strange results. In this part of the code add the line in red;

For Each varItm In lstTAssignDt.ItemsSelected
[COLOR="Red"]Debug.Print lstTAssignDt.ItemData(varItm)[/COLOR]
dTeach_PIP = "#" & Format(lstTAssignDt.ItemData(varItm), "mm/dd/yyyy") & "#"
Next varItm

And tell us what you get as a result in the immediate window. (Ctrl+G in the vba editor)

I wonder that the for .. next is passed when an item is selected. Why?

 

[Minty]

To add to Pat's words of wisdom, I would only format dates when you need to see it that way, e.g. in the report or form.

Doing it at a table level can lead to a whole host of weird issues.

What were the results of the debug window?

 

[NT100]

I've removed "Medium Date" from "General" tab of its table structure. However, I still got the following time stamp.

Immediate windows
? dTeach_PIP
12:00:00 AM


I wonder that the for .. next is skipped (Not PASSED) when an item is selected. Why?

For Each varItm In lstTAssignDt.ItemsSelected
Debug.Print lstTAssignDt.ItemData(varItm)
dTeach_PIP = "#" & Format(lstTAssignDt.ItemData(varItm), mm/dd/yyyy") & "#"
Next varItm


Need help on this as well.

Thanks

 

[NT100]

What do you mean by Control Set? I don't get the meaning of your message.

 

[Minty]

You still haven't confirmed what the immediate window is showing here;

For Each varItm In lstTAssignDt.ItemsSelected
[COLOR="Red"]Debug.Print lstTAssignDt.ItemData(varItm)[/COLOR]
dTeach_PIP = "#" & Format(lstTAssignDt.ItemData(varItm), mm/dd/yyyy") & "#"
Next varItm

If you can't see anything change that line to
Debug.Print "Your list value is : " & NZ(lstTAssignDt.ItemData(varItm),"Null Value"

 

[NT100]

same display at immediate windows

Immediate windows
? dTeach_PIP
12:00:00 AM

However, the For ... Next below is still skipped

For Each varItm In lstTAssignDt.ItemsSelected
Debug.Print lstTAssignDt.ItemData(varItm)
dTeach_PIP = "#" & Format(lstTAssignDt.ItemData(varItm), "mm/dd/yyyy") & "#"
Next varItm


Instead, I use dTeach_PIP = lstTAssignDt.ItemData(lstTAssignDt.ListIndex()), it displays the date properly.

Thanks a lot

 

[NT100]

No. nothing applied in the Format PROPERTY? See attached files

 

[NT100]

Internally, dates are stored as double precision numbers so today's date and time =

Print cdbl(now())
43032.6312847222
print now()
10/24/2017 3:09:13 PM

NEVER, EVER format a date if you expect to use it for anything except display. As long as you leave a date as a date data type, it will always act like a date and you won't have a problem with differing human desired formats because internally the date is simply a serial number and it doesn't matter whether it is formatted ymd, dmy, or mdy. Once you format it with the Format() function, you convert it to a string at which point, it will act like a string rather than a number. Sorting a date that has been formatted as dd-mmm-yyyy will put all first month days first so assuming you had a list of all the days of the year, the list would start with:
01-Apr-2017
01-Aug-2017
01-Feb-2017
01-Jan-2017
01-Jul-2017
01-Jun-2017
,,,,
02-Apr-2017
02-Aug-2017

Got the concept clearly