Retrieve Table Column based on Date Calculation (Command button)

[X2600]

Hello All,

I've been working on a project for some time now - a ticket tracker to be precise. There are three fields in the table:

Initial Request Date
Followup Request Date
Ticket Identification Number

I've been trying to write VBA code that will retrieve the ticket numbers that were submitted in the last 24/48/All hours (Based on Optionboxes and a Command button). Those ticket numbers are to be sent to a listbox's rowsource for requery; however, I am unable to get the listbox to show any results.

Private Sub Command74_Click()
Dim Variable_Fol24 As String
Dim Variable_Fol48 As String
Dim Variable_Folall As String

Variable_Fol24 = "SELECT Tracker.TicketNumber, Tracker.[TicketNumber] FROM Tracker WHERE Tracker.[Initial] =< DATE_FORMAT(NOW(),'%Y%m%d')-1;"
Variable_Fol48 = "SELECT Tracker.TicketNumber, Tracker.[TicketNumber] FROM Tracker WHERE Tracker.[Initial] > DATE_FORMAT(NOW(),'%Y%m%d')-1;"
Variable_Folall = "SELECT TicketNumber FROM Tracker"

If Option58.DefaultValue = "10" Then List134.RowSource = Variable_Fol24
If Option60.DefaultValue = "10" Then List134.RowSource = Variable_Fol48
If Option63.DefaultValue = "10" Then List134.RowSource = Variable_Folall
Me.List134.Requery
MsgBox (List134.RowSource)

End Sub

The messagebox is just used to verify that the rowsource is passing over correctly, which it does.

So, I hope there is at least one guru out there that will graciously show me how to correct this code.

 

[dhookom]

I'm not sure why you are using the "DefaultValue" or why you have two columns in your list box. Typically you would check the value of the option group, not option button values like:

Dim strWhere as String
Dim strSQL as String
strSQL = "SELECT TicketNumber, TicketNumber FROM Tracker "
Select Case Me.YourOptionGroupCtrlName
  Case 1
    strWhere = " WHERE Initial >= DateAdd('h',-24,Now())"
  Case 2
    strWhere = " WHERE Initial >= DateAdd('h',-48,Now())"
  Case 3 'all records
    strWhere = " WHERE 1=1 "
End Select
strSQL = strSQL & strWhere & " ORDER BY 1"
debug.print strSQL
Me.List34.RowSource = strSQL

You should also consider changing the name of List34 to something that makes sense.

Duane
Hook'D on Access
MS Access MVP

 

[X2600]

Firstly, thank you very much for your help. I look forward to trying the code, and implementing the technique you've used in future projects.

however...

This may seem like a rediculous question, but how do I create an options group? I have three options, each created individually. I must be blind, because I can't seem to find the grouped creation control method.

 

[X2600]

Cancel that... I had to open both eyes to see it.

 

[X2600]

Good news and bad newbs... the good news is, the third query works perfectly. The bad news... the first two do not. I modified the code a bit, to try to suit the needs of the tables. The format for the date of "Initial" is yyyymmdd, which makes the calculation pretty simple, but it doesn't seem to run through:

Dim strWhere As String
Dim strSQL As String
strSQL = "SELECT TicketNumber, TicketNumber FROM Tracker "
Select Case Me.OptionGroup
Case 1
strWhere = " WHERE Initial <= DateAdd('yyyymmdd',-2,Now())"
Case 2
strWhere = " WHERE Initial >= DateAdd('yyyymmdd',-2,Now())"
Case 3 'all records
strWhere = " WHERE 1=1 "
End Select
strSQL = strSQL & strWhere & " ORDER BY 1"
Debug.Print strSQL
Me.listFollowup.RowSource = strSQL
End Sub

Perhaps the time is getting stamped onto the end of the query calculation? As of now, the first two options return nothing. Any pointers?

btw - I did as you recommended, and changed the list name. Much easier to find in the code.

 

[dhookom]

Why did you change the first argument in the DateAdd() function to 'yyyymmdd'? This isn't a valid argument.

Did you check the debug window (press Ctrl+G)?


Duane
Hook'D on Access
MS Access MVP

 

[X2600]

Yes Sir, and I saw the code rejected in the debug window. So my question, then, is as follows:

Given the two variables:

Initial: yyyymmdd (It is stored in the table in this format)
DateAdd Variable (Which is in a format I'm unfamiliar with)

How can I compare the two variables? I would need to change the DateAdd format to yyyymmdd - Is this possible? If so, how can it be done?

 

[dhookom]

Earlier I thought you stated Initial is a date field. If it's a date field, then you can use date functions with it. If it is text, then you need to convert it to a date.

Duane
Hook'D on Access
MS Access MVP

 

[X2600]

How would I convert this field to a date field? Instead of dim as string, dim as date?

 

[dhookom]

If you have text values like "20101129" then you just change the data type so you save yourself some work and do it right. I don't have much patience for storing dates in text fields.

You can use an expression like:

DateSerial(Val(Left(Initial ,4)),Val(Mid(Initial ,5,2)),Val(Right(Initial ,2)))

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

Dim strWhere As String
Dim strSQL As String
strSQL = "SELECT TicketNumber, TicketNumber FROM Tracker "
Select Case Me.OptionGroup
  Case 1
    strWhere = " WHERE Initial >= Format(DateAdd('h',-24,Now()), 'yyyymmdd')"
  Case 2
    strWhere = " WHERE Initial >= Format(DateAdd('h',-48,Now()), 'yyyymmdd')"
  Case 3 'all records
    strWhere = " "
End Select
strSQL = strSQL & strWhere & " ORDER BY 1"
Debug.Print strSQL
Me.listFollowup.RowSource = strSQL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[X2600]

Very close... that was an excellent suggestion btw. I did fool around a bit with Date_Format, with no luck. For future reference:

Private Sub Command74_Click()
FOL.Visible = True
FOL.SetFocus

Dim strWhere As String
Dim strSQL As String
strSQL = "SELECT TicketNumber, TicketNumber FROM Tracker "
Select Case Me.OptionGroup
Case 1
strWhere = " WHERE Initial <= Format(DateAdd('d',-1,Now()), 'yyyymmdd')"
Case 2
strWhere = " WHERE Initial >= Format(DateAdd('d',-2,Now()), 'yyyymmdd')"
Case 3 'all records
strWhere = " WHERE 1=1 "
End Select
strSQL = strSQL & strWhere & " ORDER BY 1"
Debug.Print strSQL
Me.listFollowup.RowSource = strSQL
End Sub

... is the working code. I simply changed the h to d, and everything executed perfectly. Thank you all for your attention and suggestions... it has been a learning experience.