Creating a query in VBA help?

[offie]

I'm new to this and am having a hard time finding out how to do this, if you have a link to a tutorial or something that would be super helpful too.

What I want to do is make a query from selections on a form, it looks like this:

I want to use DateDiff =DateDiff("d", [DateFrom], [DateTo])

With the dates defined by what field the user had checkmarked. Something that I have used previously is:

If Check1 = -1 Then
        strSQL = strSQL & "[ProjectCode],"
    End If

For the fiscal year selection I want to calculate the FY from the selected DateFrom using
IIf(DatePart("q",DateAdd("m",-3,[DateFrom]))=4,DatePart("yyyy",[DateFrom])-1,DatePart("yyyy",[DateFrom]))

I also want to display the quarter using
DatePart("q",DateAdd("m",-3,[DateFrom]))

Use only rows where the fiscal year will be, Between [Forms]![FormName].[FromFY] AND [Forms]![FormName].[ToFY]

My issue is that I have no idea how to set this up or what commands to use, has anyone done something like this before or have any relevant resources?

Thanks!

[offie]

I've thought about it some more and I think what would work best (if possible) to is assign fields to variables DateFrom and DateTo and then just make a simple SQL query using those variables

Is it possible to do that? Something along the lines of
If Check1=-1 then (Assign?) [Field1] (As?) [DateFrom]

and then somehow make it so that only 1 checkbox per line can be selected?

[rpeare]

I don't think you can set up a SINGLE query to do this. You'd likely have to get VBA involved or create a different query for each possible combination of dates then change the record source of any report when you run it which is a bit of a pain in the butt.

If you create the record source (query) on the fly you'd have to do much the same thing but you wouldn't have to store a bunch of objects (different queries) for each possibility.

if you're doing ONE calculation (for instance just looking at the current record) you could do something like you mention but I would set your two columns of check boxes up as OPTION GROUPS. That automatically prevents you from having more than one item selected per group (you'd have two option groups 1 for your from, one for you to).

Then you could have a formula like

datediff("d", (iif([option1] = 1, [DateAssigned], iif([option1] = 2, [senttocontractor], etc)), (iif([option2] = 1, [senttocontractor], iif([option2] = 2, [recfromcontractor], etc)))

[offie]

That was kind of the point, I want to do this in VBA
So far I'm thinking of using something like this for all the check boxes
If CheckAssigned = -1 Then
dim DateFrom as string
DateFrom = dlookup("[DateAssigned]","dbo_ChangeOrder")
End If
If CheckToContractor = -1 Then
dim DateTo as string
DateFrom = dlookup("[COToContractor]","dbo_ChangeOrder")
End If

What I'm having issues with is setting up the strSQL, how can I put the DateFrom and DateTo strings into the SQL? or is using the dlookup not possible in this situation?

[rpeare]

dim DateFrom as string
dim DateTo as string

if Option1 = 1 Then
  DateFrom = "Date_Assigned"
elseif Option1 = 2 Then
  DateFrom = "Sent_to_Contractor"
elseif
...remaining options
end if

If Option2 = 1 Then
   DateTo = "Sent_to_Contractor"
elseif Option2 = 2 Then
   DateTo = "Rec_from_Contractor"
else
...remaining options
endif

sSQL = "SELECT datediff("""d""", [" & datefrom & "], [" & DateTo & "]) AS DateDifference FROM tblTableName"

You may have to play around with the " marks around the d in the datediff function because of the way " are handled when they are part of a constructed string but I think the formula may be correct.

[Dal Jeanis]

1) I'd make those checkboxes into radio buttons in a frame. You can only have one "From" and one "To", so checkboxes aren't the right interface.

2) In the AfterUpdate of each frame and each fiscal year textbox , you call a single subroutine that will rebuild the entire query based upon the values of all four controls.

3) Start off by coding VBA to build the SQL for a query that works for just one of the above pairs of conditions. Test the SQL.

4) Then, replace the start and end dates with local variables, and just above that point, set the variables based upon the values of the frames. Test a couple of combinations. Viola! You're done. (Alternately, you could use temp variables and set those variables in the afterupdate of the frame, before calling the buildquery module.)

[offie]

Thanks a bunch! It makes sense except for the after update part, what code do I put in for the after update?
Then would I put the call funtion in my command click (has the sql) sub?

This is all I've got so far

Option Compare Database
Private Sub Frame206_AfterUpdate()
Dim DateFrom As String
If Me.Frame206.Value = 1 Then
        DateFrom = DateAssigned
    ElseIf Me.Frame206.Value = 2 Then
        DateFrom = COToContractorDate
    ElseIf Me.Frame206.Value = 3 Then
        DateFrom = COFromContractorDate
    ElseIf Me.Frame206.Value = 4 Then
        DateFrom = COToCostControlDate
    ElseIf Me.Frame206.Value = 5 Then
        DateFrom = COFromCostControlDate
    ElseIf Me.Frame206.Value = 6 Then
        DateFrom = COToAgencyDate
    ElseIf Me.Frame206.Value = 7 Then
        DateFrom = COFromAgencyDate
    ElseIf Me.Frame206.Value = 8 Then
        DateFrom = COToOSCRDate
    ElseIf Me.Frame206.Value = 9 Then
        DateFrom = COFromOSCRDate
    End If

End Sub
Private Sub Frame296_AfterUpdate()
Dim DateTo As String
If Me.Frame296.Value = 1 Then
        DateTo = COToContractorDate
    ElseIf Me.Frame296.Value = 2 Then
        DateTo = COFromContractorDate
    ElseIf Me.Frame296.Value = 3 Then
        DateTo = COToCostControlDate
    ElseIf Me.Frame296.Value = 4 Then
        DateTo = COFromCostControlDate
    ElseIf Me.Frame296.Value = 5 Then
        DateTo = COToAgencyDate
    ElseIf Me.Frame296.Value = 6 Then
        DateTo = COFromAgencyDate
    ElseIf Me.Frame296.Value = 7 Then
        DateTo = COToOSCRDate
    ElseIf Me.Frame296.Value = 8 Then
        DateTo = COFromOSCRDate
    ElseIf Me.Frame296.Value = 9 Then
        DateTo = IssueDate
    End If

End Sub
Private Sub CODateFYFrom_AfterUpdate()
Dim YearFrom As String
End Sub
Private Sub CODateFYTo_AfterUpdate()
Dim YearTo As String
End Sub
Private Sub Command203_Click()
Dim srtSQL As String
    strSQL = "SELECT dbo_ChangeOrder.ProjectCode, dbo_ChangeOrder.TradeCode, dbo_ChangeOrder.ChangeOrderCode, dbo_ChangeOrder.DateAssigned, dbo_ChangeOrder.COToContractorDate, IIf(DatePart(""q"",DateAdd(""m"",-3,[DateAssigned]))=4,DatePart(""yyyy"",[DateAssigned])-1,DatePart(""yyyy"",[DateAssigned])) AS [Fisca lYear], DatePart(""q"",DateAdd(""m"",-3,[DateAssigned])) AS Quarter, DateDiff(""d"",[DateAssigned],[COToContractorDate]) AS Days FROM dbo_ChangeOrder WHERE (((IIf(DatePart(""q"",DateAdd(""m"",-3,[DateAssigned]))=4,DatePart(""yyyy"",[DateAssigned])-1,DatePart(""yyyy"",[DateAssigned]))) Between [Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form].[CODateFYFrom] And [Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form].[CODateFYTo]));"
    
'   Do view code before applying, uncomment line below
    MsgBox strSQL
    
'   Assign SQL code to Query
    CurrentDb.QueryDefs("CODateQuery").SQL = strSQL
    
'   Open query to view results
    DoCmd.OpenQuery "CODateQuery", acViewNormal, acEdit
End Sub

Am I on the right track?

[rpeare]

is the string returned by debug.print strSQL a valid SQL statement (can you paste the statement into a query window, run the query and get the proper result)?

It looks to me like you're only using 2 dates in the query, DateAssigned and CoToContractorDate and you're checking for values between the two dates on your form only in the DateAssigned field.. I thought you were trying to calculate the difference between two date fields on the same row and those date fields would be chosen by your form.

[offie]

It is, but this is because I haven't gotten around to adding the variables to the command sub, right now what I'm having trouble with is getting the variables from the other subs, I made them public and used the call function but to test it I put a msgbox underneath and it comes up blank

Public Sub Frame206_AfterUpdate()
Dim DateFrom As String
If Me.Frame206.Value = 1 Then
        DateFrom = DateAssigned
    ElseIf Me.Frame206.Value = 2 Then
        DateFrom = "COToContractorDate"
    ElseIf Me.Frame206.Value = 3 Then
        DateFrom = "COFromContractorDate"
    ElseIf Me.Frame206.Value = 4 Then
        DateFrom = "COToCostControlDate"
    ElseIf Me.Frame206.Value = 5 Then
        DateFrom = "COFromCostControlDate"
    ElseIf Me.Frame206.Value = 6 Then
        DateFrom = "COToAgencyDate"
    ElseIf Me.Frame206.Value = 7 Then
        DateFrom = "COFromAgencyDate"
    ElseIf Me.Frame206.Value = 8 Then
        DateFrom = "COToOSCRDate"
    ElseIf Me.Frame206.Value = 9 Then
        DateFrom = "COFromOSCRDate"
    End If
MsgBox DateFrom

End Sub
Public Sub Frame296_AfterUpdate()
Dim DateTo As String
If Me.Frame296.Value = 1 Then
        DateTo = "COToContractorDate"
    ElseIf Me.Frame296.Value = 2 Then
        DateTo = "COFromContractorDate"
    ElseIf Me.Frame296.Value = 3 Then
        DateTo = "COToCostControlDate"
    ElseIf Me.Frame296.Value = 4 Then
        DateTo = "COFromCostControlDate"
    ElseIf Me.Frame296.Value = 5 Then
        DateTo = "COToAgencyDate"
    ElseIf Me.Frame296.Value = 6 Then
        DateTo = "COFromAgencyDate"
    ElseIf Me.Frame296.Value = 7 Then
        DateTo = "COToOSCRDate"
    ElseIf Me.Frame296.Value = 8 Then
        DateTo = "COFromOSCRDate"
    ElseIf Me.Frame296.Value = 9 Then
        DateTo = "IssueDate"
    End If
MsgBox DateTo
End Sub
Public Sub CODateFYFrom_AfterUpdate()
Dim YearFrom As String
End Sub
Public Sub CODateFYTo_AfterUpdate()
Dim YearTo As String
End Sub
Private Sub Command203_Click()
Call Frame206_AfterUpdate
Call Frame296_AfterUpdate
Call CODateFYFrom_AfterUpdate
Call CODateFYTo_AfterUpdate
MsgBox DateFrom
MsgBox DateTo
Dim srtSQL As String
    strSQL = "SELECT dbo_ChangeOrder.ProjectCode, dbo_ChangeOrder.TradeCode, dbo_ChangeOrder.ChangeOrderCode, dbo_ChangeOrder.DateAssigned, dbo_ChangeOrder.COToContractorDate, IIf(DatePart(""q"",DateAdd(""m"",-3,[DateAssigned]))=4,DatePart(""yyyy"",[DateAssigned])-1,DatePart(""yyyy"",[DateAssigned])) AS [Fisca lYear], DatePart(""q"",DateAdd(""m"",-3,[DateAssigned])) AS Quarter, DateDiff(""d"",[DateAssigned],[COToContractorDate]) AS Days FROM dbo_ChangeOrder WHERE (((IIf(DatePart(""q"",DateAdd(""m"",-3,[DateAssigned]))=4,DatePart(""yyyy"",[DateAssigned])-1,DatePart(""yyyy"",[DateAssigned]))) Between [Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form].[CODateFYFrom] And [Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form].[CODateFYTo]));"
    
'   Do view code before applying, uncomment line below
    MsgBox strSQL
    
'   Assign SQL code to Query
    CurrentDb.QueryDefs("CODateQuery").SQL = strSQL
    
'   Open query to view results
    DoCmd.OpenQuery "CODateQuery", acViewNormal, acEdit
End Sub

[Dal Jeanis]

In each of the controls, you set a temp variable:

 
Private Sub Frame206_AfterUpdate()
Dim DateFrom As String
   Select Case Frame206.Value
   Case 1
      TempVars.Add "tvFromField", "[DateAssigned]"
   Case 2
      TempVars.Add "tvFromField", "[COToContractorDate]"
   Case 3
      TempVars.Add "tvFromField", "[3rd radio button field]"
   Case Else
      TempVars.Add "tvFromField", "[DateAssigned]" 
   End select
End Sub

Private Sub Frame296_AfterUpdate()
Dim DateTo As String
   Select Case Frame296.Value
   Case 1
      TempVars.Add "tvToField", "[COToContractorDate]"
   Case 2
      TempVars.Add "tvToField", "[2nd radio button field]"
   Case 3
      TempVars.Add "tvToField", "[3nd radio button field]"
   Case Else
      TempVars.Add "tvToField", "[COToContractorDate]"
   End select
End Sub

Private Sub CODateFYFrom_AfterUpdate()
Dim YearFrom As String
   TempVars.Add "tvDateFYFRom", CODateFYFRom
End Sub

Private Sub CODateFYTo_AfterUpdate()
Dim YearTo As String
   TempVars.Add "tvDateFYTo", CODateFYTo
End Sub

Then when you build the SQL, you replace the hard-coded [DateAssigned] with one temp field, the hardcoded [COToContractorDate] with the second, and so on.
You'll need to remember to set the initial values somewhere, perhaps the load event.

Private Sub Command203_Click()
Dim srtSQL As String

    strSQL = "SELECT CO.ProjectCode, CO.TradeCode, CO.ChangeOrderCode, CO." & _
              [TempVars].[tvFromField] & " , CO." & [TempVars].[tvToField] & _
             ", IIf(DatePart(""q"",DateAdd(""m"",-3," & [TempVars].[tvFromField] & _
             "=4,DatePart(""yyyy""," & [TempVars].[tvFromField] & ")-1," & _
             "DatePart(""yyyy""," & [TempVars].[tvFromField] & ")) AS [FiscalYear]," _
             "DatePart(""q"",DateAdd(""m"",-3," & [TempVars].[tvFromField] & ")) AS Quarter," & _ 
             "DateDiff(""d""," & [TempVars].[tvFromField] & ","[TempVars].[tvToField]") AS Days " & _  
             "FROM dbo_ChangeOrder AS CO  WHERE (((IIf(DatePart(""q"",DateAdd(""m"",-3," & _ 
              [TempVars].[tvFromField] & "))=4,DatePart(""yyyy""," & [TempVars].[tvFromField] & _
              ")-1,DatePart(""yyyy""," & [TempVars].[tvFromField] & "))) Between " & _ 
              [TempVars].[tvDateFYFRom] & " And " & [TempVars].[tvDateFYFRom] & "));"