VBA Problem with Between 2 dates

[brain1606]

Having problems updating code. I need it to look at multiple Origin_Date columns (Origin_Date2 & Origin_Date3).
Current VBA

     ' If Opened Date From
     If (IsDate(Me.OpenedDateFrom) And IsDate(Me.OpenedDateTo)) Then
    strWhere = strWhere & " AND  [CAReport.Origin_Date] BETWEEN #" & Format(Me.OpenedDateFrom, "mm\/dd\/yyyy") & _
            "# AND #" & Format(Me.OpenedDateTo, "mm\/dd\/yyyy") & "#"
     ElseIf Nz((Me.OpenedDateFrom) And (Me.OpenedDateTo)) <> "" Then
       strError = cInvalidDateError
  End If

I also tried

strWhere = strWhere & " AND " & "[CAReport.Origin_Date] >= " & GetDateFilter(Me.OpenedDateFrom) _
& " OR [CAReport.Origin_Date2] >= " & GetDateFilter(Me.OpenedDateTo) _
& " OR CAReport.Origin_Date3] >= " & GetDateFilter(Me.OpenedDateTo)

 

[theDBguy]

Hi. Welcome to AWF!

The "real" problem with your table structure is you seem to have a repeating group of columns, which is against "normalization" rules. If you fix the table structure, it would be easier to query the data you need from it. To fix your current situation, your syntax would probably have to look something like this:

strWhere = strWhere & " AND ((Origin1 Between FromDate And ToDate) OR (Origin2 Between FromDate And ToDate) OR (Origin3...))

 

[brain1606]

Its nice being here. going crazy with this code.

" repeating group of columns"? Some times we have more dates for the same record.

code was what I was thinking but cant get it to work right.

 

[brain1606]

I think i got it...testing

 

[Minty]

@brain1606 Ideally you if you can have a variable number of data points for something you should record them in a child table related back to the main record. if you don't and Origin6 arrives you have to redesign the whole thing and redo you forms/ queries etc. to accommodate it.

e.g. Order lines in an order. They would be in a child table related to the Order header.

 

[brain1606]

I get what you are saying but didn't want to have multiple tables for a form. Might update in the future

 

[brain1606]

Thank you so much its working

 

[theDBguy]

Thank you so much its working

Hi. Congratulations! Glad to hear you got it to work. Good luck with your project.

 

[Pat Hartman]

If you don't need time as part of these dates, use Date() rather than Time() to populate them. If you want to keep time, then you need to format all three fields to remove time.

05-27-2020 3:30 PM is NOT between 05-26-2020 and 05-27-2020