VBA Problem with Between 2 dates (1 Viewer)

brain1606

New member
Local time
Today, 17:52
Joined
May 27, 2020
Messages
5
Having problems updating code. I need it to look at multiple Origin_Date columns (Origin_Date2 & Origin_Date3).
Current VBA


Code:
     ' 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
Code:
strWhere = strWhere & " AND " & "[CAReport.Origin_Date] >= " & GetDateFilter(Me.OpenedDateFrom) _
& " OR [CAReport.Origin_Date2] >= " & GetDateFilter(Me.OpenedDateTo) _
& " OR CAReport.Origin_Date3] >= " & GetDateFilter(Me.OpenedDateTo)
 
Last edited by a moderator:

theDBguy

I’m here to help
Staff member
Local time
Today, 14:52
Joined
Oct 29, 2018
Messages
21,358
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:
SQL:
strWhere = strWhere & " AND ((Origin1 Between FromDate And ToDate) OR (Origin2 Between FromDate And ToDate) OR (Origin3...))
 

brain1606

New member
Local time
Today, 17:52
Joined
May 27, 2020
Messages
5
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.
 

Minty

AWF VIP
Local time
Today, 21:52
Joined
Jul 26, 2013
Messages
10,355
@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

New member
Local time
Today, 17:52
Joined
May 27, 2020
Messages
5
I get what you are saying but didn't want to have multiple tables for a form. Might update in the future
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:52
Joined
Feb 19, 2002
Messages
42,976
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
 

Users who are viewing this thread

Top Bottom