1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Date Parameter Failing in Query from calculated date field using DateValue Ac

Discussion in 'Business Applications' started by SiobhanP, Aug 25, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. SiobhanP

    SiobhanP Thread Starter

    Joined:
    Jun 22, 2010
    Messages:
    26
    First the background: We are gradually deploying Office 2007, but not everyone in the agency has it. I have both 2003 and 2007 installed on my system, but I do my work in 2003, and none of our databases have actually been converted to 2007 format (at least, none that I work with). There are occasionally people who use 2007 when viewing databases, but I don't know if any of the people who are working in this particular database are using 2007. The problem is reproducible in 2003 and so I've been trying to fix it here. The query behind the problem report used to work. It has been a problem for several months (it is run monthly, and every month for the past five they've had problems with it) and I've finally gotten to the root of the actual issue.

    The purpose of the report is to generate invoice for outstanding loans. The DateNextPayment is a calculated field based on the payment plan for the loan. It is calculated using a function in the query named qryGetSitesDDInvoices1. This is the function that is called (I did not write this function, I'm supporting this db long after the original writer has left):

    Code:
    Function DateNextPayment(PaymentSchedule As String, DateFirstPayment As Date, Balance As Currency)
    
        On Error GoTo Handle_err
            
        Dim Date1 As Date
        Dim Date2 As Date
        Dim Date3 As Date
        Dim NextQtrDate As Date
          
            'The code adds one month to datenextpayment at start of each month.  Or yearly or weekly
        
             Select Case PaymentSchedule
    
              Case "Weekly"
                   DateNextPayment = DateSerial(DatePart("yyyy", Date), DatePart("m", Date), DatePart("d", Date) + 7)
    
              Case "Monthly"
                   DateNextPayment = DateSerial(DatePart("yyyy", Date), DatePart("m", Date) + 1, 15)
            
              Case "Quarterly"
                ' determine the first day of the next quarter that follows
                ' the one that we are currently within
                
                 Select Case DatePart("q", Date)
                     Case 1
                         NextQtrDate = DateSerial(DatePart("yyyy", Date), 4, 1)
                     Case 2
                         NextQtrDate = DateSerial(DatePart("yyyy", Date), 7, 1)
                     Case 3
                         NextQtrDate = DateSerial(DatePart("yyyy", Date), 10, 1)
                     Case 4
                         NextQtrDate = DateSerial(DatePart("yyyy", Date) + 1, 1, 1)
                 End Select
                 
             If DateFirstPayment > Date Then
      ' the date case the first payment is sometime in the future
                If DateFirstPayment <= NextQtrDate Then
      ' the first payment is between now and the beginning case the
      ' next quarter,so set the nextpayment date to the beginning case the next quarter
    
                     DateNextPayment = NextQtrDate
             Else
       ' the firstpayment is futher forward than the very next quarter's beginning
                    Select Case DatePart("q", DateFirstPayment)
                        Case 1
                        DateNextPayment = DateSerial(DatePart("yyyy", DateFirstPayment), 1, 1)
                        Case 2
                        DateNextPayment = DateSerial(DatePart("yyyy", DateFirstPayment), 4, 1)
                        Case 3
                        DateNextPayment = DateSerial(DatePart("yyyy", DateFirstPayment), 7, 1)
                        Case 4
                        DateNextPayment = DateSerial(DatePart("yyyy", DateFirstPayment), 10, 1)
                    End Select
             End If
                Else
      ' the date case the first payment is sometime in the past
      ' since we are working quarterly, whatever the next quarter
      ' is in the future is the one we want to assign as the next
      ' payment date
                    DateNextPayment = NextQtrDate
                End If
                    
              Case "SemiAnnually"
                    
                    If DatePart("m", DateFirstPayment) < 7 Then ' example is 2/1/94
                        Date1 = DateSerial(DatePart("yyyy", Date), DatePart("m", DateFirstPayment), 15) ' 2/1/2001
                        Date2 = DateSerial(DatePart("yyyy", Date), DatePart("m", DateFirstPayment) + 6, 15) ' 8/2/2001
                        Date3 = DateSerial(DatePart("yyyy", Date) + 1, DatePart("m", DateFirstPayment), 15) ' 2/1/2002
                    Else ' example is 11/1/94
                        Date1 = DateSerial(DatePart("yyyy", Date), DatePart("m", DateFirstPayment) - 6, 15) ' 5/1/2001
                        Date2 = DateSerial(DatePart("yyyy", Date), DatePart("m", DateFirstPayment), 15)  ' 11/1/2001
                        Date3 = DateSerial(DatePart("yyyy", Date) + 1, DatePart("m", DateFirstPayment) - 6, 15) ' 5/1/2002
                   End If
                   
                   If Date < Date1 Then
                      DateNextPayment = Date1
                   End If
                   
                   If Date1 <= Date And Date < Date2 Then
                      DateNextPayment = Date2
                   End If
                   
                   If Date >= Date2 Then
                      DateNextPayment = Date3
                   End If
                  
              Case "Annually"
                    If DatePart("m", DateFirstPayment) < DatePart("m", Date) Then
                        DateNextPayment = DateSerial(DatePart("yyyy", Date) + 1, DatePart("m", DateFirstPayment), 15)
                    Else
                        DateNextPayment = DateSerial(DatePart("yyyy", Date), DatePart("m", DateFirstPayment), 15)
                    End If
            End Select
    
            If Balance < 1 Then
                    DateNextPayment = Null
            End If
            
            If DateFirstPayment > DateNextPayment Then
                DateNextPayment = DateFirstPayment
            End If
            
    ExitHere:
        Exit Function
        
    Handle_err:
        MsgBox "ERROR!" & " " & Err.Description
        Resume ExitHere
        
    End Function
    There are no NULL records feeding into this function. The expected records are returned, with the field calculated, however, DateNextPaymentDue is coming out as TEXT not as a date. It's left aligning in the query result, not right aligning, like it would for a date. The next query is qryGetSitesDDInvoices, which starts off with qryGetSitesDDInvoices1 then adds parameters. In that query I put DateValue() on the incoming DateNextPaymentDue so that it's coming in as a date, instead of text. When I don't use any parameters on it, and just run the query, it displays as right aligned, the way a date should. However, when I try to apply this parameter: <=[Enter the 15th of the next month] (or just put in <=#09/15/2010#) it gives me a "data type mismatch in criteria expression" error.

    I have researched this, and I have tried re-registering the various libraries on the off chance it might be the function wasn't working properly. I didn't think that was it, since it was coming through as right aligned, and this has not solved the problem. I am in a quandry.

    I really need some help for this because I would very much prefer NOT having to store this date somewhere. This SHOULD WORK! And it HAS worked in the past!

    Maybe there's some library I should have registered that I don't have since we now have 2007 running around our agency?
     
  2. Sponsor

  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,412
    SiobhanP, it is not a library reference that is the problem, it is the fact that the Function returns a String rather than a date. Sometimes Access will recognise a text string as a date, but not always.
    So the answer is to try and force Access to recognise it as a Date and your Datevalue should do that.
    Have you tried applying the datevalue to the [Enter the 15th of the next month]?
    Can you post a copy of the database.
     
  4. SiobhanP

    SiobhanP Thread Starter

    Joined:
    Jun 22, 2010
    Messages:
    26
    I have tried that in the past. I just tried putting DateValue around my parameter and it gives me this error:

    "The expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

    There is confidential data in this database, I cannot post it in its entirety.
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,412
    It would not need any data in it, just a copy of the tabeles, query & Function..
    I can add a couple of dummy records to test the Query & Function.
     
  6. SiobhanP

    SiobhanP Thread Starter

    Joined:
    Jun 22, 2010
    Messages:
    26
    Well, not to be annoying, but it is also an Access front end on top of an SQL backend (which is a fact I should have mentioned at the outset, I'd forgotten it had an SQL backend until just now). *grumble*
     
  7. SiobhanP

    SiobhanP Thread Starter

    Joined:
    Jun 22, 2010
    Messages:
    26
    So, here's a funny thing. I created an Access table from the data in the SQL Linked table that's feeding these queries. Then I used that instead of the linked table, and the query worked with the parameter.

    Problem is, I CAN'T DO THIS. I have GOT to remain linked to the SQL backend. Does that help inform anything?
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,412
    Yes it does a bit, SQL handles it's data differently to Access.
    Can you write an Append Query to put the SQL data in to a Temporary Access table to work with?
     
  9. SiobhanP

    SiobhanP Thread Starter

    Joined:
    Jun 22, 2010
    Messages:
    26
    I was really hoping to avoid that, but I suppose I don't have a choice.
     
  10. SiobhanP

    SiobhanP Thread Starter

    Joined:
    Jun 22, 2010
    Messages:
    26
    See, here's the thing I do NOT understand. The data from the table has NOTHING TO DO with the data coming out from the function at all. The function is building a date next payment due from today's date. NONE of the dates in the table itself are being used after that point. It looks at what the payment schedule is, and if it's monthly, it builds the date from Date().
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,412
    Sorry, I don't understand it either.
    Can you format the Function date as a date?
     
  12. SiobhanP

    SiobhanP Thread Starter

    Joined:
    Jun 22, 2010
    Messages:
    26
    GRAH!!! Sorry, I've been banging my head against this for four days. I tried added "as date" at the end of the function, then I saved it. Then when I went to run the query, it tells me some OTHER function (which is RIGHT THERE) isn't defined. Yet another reason why I hate touching ANYthing on this darned db. It's so easy to just make things stop working for no good reason.
     
  13. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,412
    When you put the parameter date in, have you tried putting it in as a text ie. 09/15/2010

    to see if it will accept that and match it to the value coming back from the Function?

    You could actually write a VBA based SQL statement using the Function value and run that instead of the Query.
     
  14. SiobhanP

    SiobhanP Thread Starter

    Joined:
    Jun 22, 2010
    Messages:
    26
    yeah I've done the text thing, I've tried using cdate instead of datevalue, loads of things. I'm restarting the DB to see if it was just a munged access thing. maybe I could do this all on the sQL end... that might work... :)

    ETA: Restarting the Db cleared up that stupid "function not defined" error. *mumble*
     
  15. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,412
    One thing for you to try in an effort to try and get the to data to match up.
    Use Format([DateNextPaymentDue], "mm/dd/yyyy") in a new Column Heading and also
    Format([Enter Date], "mm/dd/yyyy") as the Parameter for that new column.
     
  16. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,412
    Or you may have to split the DateNextPaymentDue in to it's mm, dd & yyyy and then re-assemble it again.
     
  17. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/945599