Solved SQL Help..

[mloucel]

Hello All:
I have the following SQL:

Private Sub RefreshButton_Click()
'
'   Code courtesy of @Arnelgp [AWF]
'   Update courtesy of @MajP [AWF]
'   Shout out to @theDBguy for his awesome knowledge [AWF]
'
    Dim sql As String
    Dim i As Integer
    Dim Db As DAO.Database
    If IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
        ' change the record source of your form
        Set Db = CurrentDb
        sql = Db.QueryDefs("qryAuthorizationFullView").sql
        'remove the ; at the end
        sql = Replace$(sql, ";", "")
        'check to see if there is a where clause
        i = InStr(1, sql, "WHERE")
        'Keep everything before the where close
        If i <> 0 Then
            sql = Left$(sql, i - 1)
       End If
        ' was "ORDER By ReferDate"
        
       'I am assuming both date boxes have to be in or it will not filter sometimes peope will do an after or before filter if only one date entered
       'Stat true and both dates fille in
       If Me.StatCheck = True And _
            IsDate(Me.FromDate) And _
            IsDate(Me.ToDate) Then
            sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
                 "# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#" & _
                 " OR AuthorizationT.Urgent = TRUE or AuthorizationT.Pending = TRUE ORDER By AuthorizationT.Urgent, AuthorizationT.AuthorizationID"
        'Stat true but one of the dates not filled in
        ElseIf Me.StatCheck = True And Not IsDate(Me.FromDate) Or Not IsDate(Me.ToDate) Then
            sql = sql & " WHERE StatCheck = TRUE Order by AuthorizationT.AuthorizationID"
        'Stat false and both dates filled in
        ElseIf Me.StatCheck = False And IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
          sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
                    "# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#" & _
                    "ORDER By AuthorizationT.AuthorizationID"
        End If
        RecordSource = sql
    End If
    Set Db = Nothing
End Sub

I Did not create the SQL since I am NOT that advanced, credits to @arnelgp , @MajP and @theDBguy for their help in this one.

I need to ADD a new variable to this:

"Me.PendingChk=TRUE"

This is the Form:

So now if the User clicks ANY of those fields once the RecordSource is changed should work sorting by the required changes, could be any of them or All of them
Right now Dates and STAT work fine, no problem whatsoever. I just need to ADD Pending to the equation.

Any Help will be appreciated.

 

[MajP]

Not real clear since you have 3 cases
Stat is true and both dates filled in
Stat is true and one of the dates is not filled in
Stat is false and both dates filled in

If Me.StatCheck = True And IsDate(Me.FromDate) And IsDate(Me.ToDate) Then

Sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
"# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#" & _
" OR AuthorizationT.Urgent = TRUE or AuthorizationT.Pending = TRUE" & _
" ORDER By AuthorizationT.Urgent, AuthorizationT.AuthorizationID"

'Stat true but one of the dates not filled in
ElseIf Me.StatCheck = True And (Not IsDate(Me.FromDate) Or Not IsDate(Me.ToDate)) Then
sql = sql & " WHERE StatCheck = TRUE Order by AuthorizationT.AuthorizationID"

'Stat false and both dates filled in
ElseIf Me.StatCheck = False And IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
"# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#" & _
"ORDER By AuthorizationT.AuthorizationID"
end If

I reformatted this to be a little more readable.
You want to include Pending in what way and under what conditions. It is an Or condition in Stat true and dates filled in.
Is is an AND or an OR and in which condition.
Spell the expression out in plain language that you want to achieve.

 

[mloucel]

Not real clear since you have 3 cases
Stat is true and both dates filled in
Stat is true and one of the dates is not filled in
Stat is false and both dates filled in



I reformatted this to be a little more readable.
You want to include Pending in what way and under what conditions. It is an Or condition in Stat true and dates filled in.
Is is an AND or an OR and in which condition.
Spell the expression out in plain language that you want to achieve.

Not real clear since you have 3 cases
Stat is true and both dates filled in
Stat is true and one of the dates is not filled in
Stat is false and both dates filled in

Dates are filled always, and will always be last 90 days from Today all the time.

I will try to make sure that ToDate is NOT less than the FromDate, it can be = but not <, that's something I have pending.

right now if the EU clicks STAT and Refresh all Stat go first in the list.

what I need if the EU clicks Pending should show me only the pending ones at the top, I can careless if the other ones show at the end of the list, it doesn't matter.

The file you currently have has the forms, the one I need is in MenuF.

I'm going to update the files so you can see the updated files.

Authorizations.accdb

drive.google.com

The ONLY change to the table is on the AuthorizationsT has a Pending Field [Yes/No]

Thanks so much.

 

[MajP]

maybe this is what you mean

If Me.StatCheck = True And IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
         
      Sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
                  "# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#" & _
                  " OR AuthorizationT.Urgent = TRUE or AuthorizationT.Pending = TRUE"
                 
                 If Me.pending then
                   Sql = Sql & " ORDER By Pending, AuthorizationT.Urgent, AuthorizationT.AuthorizationID"
                 else
                    Sql = Sql & " ORDER By AuthorizationT.Urgent, AuthorizationT.AuthorizationID"
                end if


'Stat true but one of the dates not filled in
 ElseIf Me.StatCheck = True And (Not IsDate(Me.FromDate) Or Not IsDate(Me.ToDate)) Then
         
             sql = sql & " WHERE StatCheck = TRUE"
         
             If Me.pending then
                 Sql = Sql & " ORDER By Pending, AuthorizationT.AuthorizationID"
             else
                Sql = Sql & " ORDER By AuthorizationT.AuthorizationID"
             end if
 
            'Stat false and both dates filled in
ElseIf Me.StatCheck = False And IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
     
              sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
                          "# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#"
           
              If Me.pending then
                 Sql = Sql & " ORDER By Pending, AuthorizationT.AuthorizationID"
             else
                Sql = Sql & " ORDER By AuthorizationT.AuthorizationID"
             end if
end If

If pending is true then all cases sort by pending then their other specified fields. If not it sorts by the originally specified fields.

 

[mloucel]

maybe this is what you mean

If Me.StatCheck = True And IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
         
      Sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
                  "# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#" & _
                  " OR AuthorizationT.Urgent = TRUE or AuthorizationT.Pending = TRUE"
                 
                 If Me.pending then
                    strSql = strSql & " ORDER By Pending, AuthorizationT.Urgent, AuthorizationT.AuthorizationID"
                 else
                    strSql = strSql & " ORDER By AuthorizationT.Urgent, AuthorizationT.AuthorizationID"
                end if


'Stat true but one of the dates not filled in
 ElseIf Me.StatCheck = True And (Not IsDate(Me.FromDate) Or Not IsDate(Me.ToDate)) Then
         
             sql = sql & " WHERE StatCheck = TRUE"
         
             If Me.pending then
                 strSql = strSql & " ORDER By Pending, AuthorizationT.AuthorizationID"
             else
                strSql = strSql & " ORDER By AuthorizationT.AuthorizationID"
             end if
 
            'Stat false and both dates filled in
ElseIf Me.StatCheck = False And IsDate(Me.FromDate) And IsDate(Me.ToDate) Then
     
              sql = sql & " WHERE ReferDate Between #" & Format$(Me.FromDate, "mm/dd/yyyy") & _
                          "# AND #" & Format$(Me.ToDate, "mm/dd/yyyy") & "#"
           
              If Me.pending then
                 strSql = strSql & " ORDER By Pending, AuthorizationT.AuthorizationID"
             else
                strSql = strSql & " ORDER By AuthorizationT.AuthorizationID"
             end if
end If

If pending is true then all cases sort by pending then their other specified fields. If not it sorts by the originally specified fields.

I change it a bit strSQL does not exist so I change it to sql
- Well I change the Query to show at LOAD only the Pending ones, but is not what I need, the funny part is that if I check STAT that still works fine sending all the STAT records to the TOP of the list and the rest to the bottom, but when I click on PENDING and refresh it does absolutely nothing, I will have to dig a bit deeper on how I did the STAT, I know have thanks to you the SQL sequence I need to figure out why STAT works and why PENDING does not.

Thanks so much.

 

[Pat Hartman]

To validate the query, add a stop in the code at the end of the procedure that creates the string. Use Debug.Print to print the string to the immediate window. Copy the string to the QBE and run it. Make sure it selects the data you think it should select. If it doesn't, then make sure the data you wanted to select actually exists.

Also, I dislike creating SQL in VBA. I always use querydefs with arguments UNLESS I have some reason not to. Goes back to my COBOL days when there were no graphical tools like the QBE available and my only option was typing the query embedded into the COBOL code.

 

[mloucel]

To validate the query, add a stop in the code at the end of the procedure that creates the string. Use Debug.Print to print the string to the immediate window. Copy the string to the QBE and run it. Make sure it selects the data you think it should select. If it doesn't, then make sure the data you wanted to select actually exists.

Also, I dislike creating SQL in VBA. I always use querydefs with arguments UNLESS I have some reason not to. Goes back to my COBOL days when there were no graphical tools like the QBE available and my only option was typing the query embedded into the COBOL code.

I followed your suggestion and thanks I found the culprit, and is working as it should, I have never done anything with SQL so I need to start learning it, thanks for all the help, I am going to google querydefs and find out, and as well I am going to start or at least try to learn SQL for Access, I don't know where to start but I am sure a little google will do the trick.
As we say in French: Mercy Beaucoup Madam.

 

[Pat Hartman]

Pas de problème. I love teaching people who are interested in learning. The QBE is awkward and leaves a lot to be desired. BUT, unless you have a complicated query, it is the simplest method to use even if you ultimately want to copy the SQL string and embed it. There are web tools that will reformat your raw SQL into a string you can use in a programming language.. Now that MS has finally fixed up the behind the scenes SQL string formatting to some degree, it should be less offensive to the purists here. I've been writing SQL since the 80's. In the beginning, the only option was embedded SQL in COBOL which is exactly what you are doing now in VBA. That added an additional step to the compile and one more place to make a typo. If the SQL "compile" step failed, the COBOL compile step wasn't executed so there could be typos lurking there also. I used to fantasize about having a graphic tool like the QBE (Query By Example). In the 80's when your compile failed due to a typo, it could be hours of wasted time due to the turnaround time for batch jobs. Eventually, IBM came up with a tool you could use to test your SQL outside of your COBOL program. That saved a lot of time because you could test and clean up the query without having to do it in the context of the program. It's 40 years later, there is a better way. It is far from perfect but it is getting better.

You've encouraged me to create a video. I might have time on Thursday to start it. If I do, would you like to view it and comment? The video will include creating simple queries using QBE, adding parameters, making more complex modifications by switching to SQL view, and then running parameter queries in VBA. And some hints on how to protect your query from the QBE messing up your pretty SQL formatting and why QueryDefs are better for reuse and more efficient to run. Of course, if anyone has seen such a video, I would appreciate a link so I don't waste my time.

 

[mloucel]

Hi Pat, I've seen many videos around, ALL lack something that I consider very important as a teacher:
They left the student to wander in many things and let them go to a rabbit hole.

I used to teach Networking, A+ and Network + [not anymore], I found out that many books failed to do a simple task: FOLLOW UP or in this case BUILD UP on the example.

So I changed methodology I started with a basic premise [What is a Cat5 Cable] then from there explain why the colors, learn them, the right order, etc, then how to build a Cat5 straight thru or a crossover the most common ones, then introduce the tester, then using it, etc.
Ultimately, my pupils were able to build a full network, run cables thru different scenarios.

I think of it as building a HOUSE, start from the foundation and build from there, don't just jump from the foundation to the windows, and then to the refrigerator and the ceiling.

I have seen many SQL tutorials but there is a point where instead of continuing with 1 example they suddenly go to a different thing and since we never practice all was lost and we started a new thing, hence my stupidity in SQL.

I've seen what you are capable, and hats off to you, I would surely love to learn with you, know SQL has so many rabbit trails but I know you have done things that seems imposible so I don't doubt you can create a product worth selling if you wish, you have some very powerful friends as powerful and mighty as you, I can't wait for what you have in mind.

I am looking forward to learn with you.

 

[Pat Hartman]

If you have any links for videos you found wanting, please post them so I can see if I can figure out where they go wrong.

 

[mloucel]

There are a few this one was the easiest one but not enough:
QBE
Or this one as well same case:
QBE2
I Love Richard Rost, he is really good I have learned a lot from him as well, but sometimes I got lost.
Richard Rost