Query a number on user form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is what I have in a table (MyData) and userform has the same objects
TrackNo as text
DateOpen as date (ShortDate)
Cust as text
Reason as Memo

This is the query I have in place (Temp01)
Year: Year([DateOpen])
Month: Month([DateOpen])
HowMany: Count([Year]+[Month])

SELECT Year([DateOpen]) AS [Year], Month([DateOpen]) AS [Month],
Count([Year]+[Month]) AS HowMany
FROM MyData
GROUP BY Year([DateOpen]), Month([DateOpen]);

This is want I want to do on user form:
Person enters the DateOpen in textbox and this runs the AfterUpdate event.
The event returns HowMany value from the query and I can complete the event
by adding 1 to that value and set the TrackingNo as yymmdd-HowMany,â€000â€

What can you offer to help me with this? I have used Dcount until my head
hurts to do this without the query and still cannot seem to solve this
problem so am now trying to get help with the query method.

Thank you for your assistance.
 
The easiest way to do this is by adding 1 to what is returned by DMax().
StrNextTrackNo = Nz(DMax("[TrackNo]", "MyData", "Year([DateOPen]) = " & _
Year(Me.txtDateOpen) & " AND Month([DateOpen] = " &
Month(Me.txtDateOpen)),0) + 1
strNextTrackNo = Format(Me.txtDateOpen, "yymmdd") & _
format(strNextTrackNo,"-)000"

I would suggest you avoid using reserved words like Year and Date. Using
reserved words can create problems.
 
Back
Top