none
Expression code error RRS feed

  • Question

  • I have the following code in my query:

    Expr1: "Signed this, the " & DayString(Day([GuarIssue])) & " day of " & MonthName(Month([GuarIssue])) & " in the Year " & Year([GuarIssue]) & "."

    If the [GuarIssue] date is February 28, 2019 it is returning a value of:

    Signed this, the 27th day of February in the Year 2019.

    I am not sure why it is changing the Day from the 28th to the 27th. No matter what date is entered it takes 1 day off when it runs the expression.

    What could be causing this?

    Tuesday, March 5, 2019 4:00 PM

All replies

  • First, there are no Date Functions in ACCESS named DayString or MonthName, so are these custom functions you designed yourself?

    Try:

    "Signed this, the " & Day([GuarIssue]) & " day of " & Month([GuarIssue]) & " in the Year " & Year([GuarIssue]) & "."

    Tuesday, March 5, 2019 5:16 PM
  • This does not give me what I need, I want it to say 28th (with the th) and "February" not the month number ie: 3

    using the code you gave I get the following return:

    Signed this, the 1 day of 3 in the Year 2019.

    MonthName is in the Built-in functions of build the code in query.

    the DayString is a public function:

    Public Function DayString(InputDate As Date) As String
    Dim lngDay As Long
    Dim strDay As String

    lngDay = Day(InputDate)
    Select Case lngDay
       Case 1, 21, 31
          strDay = lngDay & "st"
       Case 2, 22
          strDay = lngDay & "nd"
       Case 3, 23
          strDay = lngDay & "rd"
       Case Else
          strDay = lngDay & "th"

    End Select
    DayString = strDay
    End Function

    Tuesday, March 5, 2019 6:04 PM
  • It appears your syntax is correct. I didn't know about the DayString code and yes, MonthName is valid. It's the [GuarIssue] field that is the issue if your dates are 1 day off.

    How does the [GuarIssue] field get its value? Does it have a default value set that could be causing this? If its 1 day off every time and the field gets its value from the Date function, is the system date correct?

    Tuesday, March 5, 2019 7:09 PM
  • [GuarIssue] is field name for "Guarantee Issue date"

    The date field a User entered date field. It does not have a default value because not every project gets a guarantee. So system date shouldn't effect it, that being said, the system date is correct.

    Another thing to note is if I set the guarantee Date to March 1, 2019 (trying to get it to say the last day of February) I get a date of March 31, 2019 returned. It is so wonky.

    Tuesday, March 5, 2019 7:41 PM
  • I tested your DayString Function. When I used 03/05/2019 it returned 30th, but when I used #03/05/2019# it returned 5th (the correct answer).
    Tuesday, March 5, 2019 10:50 PM
  • You should respect your own functions and pass them what they expect, here a date value; thus:

    Expr1: "Signed this, the " & DayString([GuarIssue]) & " day of " & MonthName(Month([GuarIssue])) & " in the Year " & Year([GuarIssue]) & "."


    Gustav Brock

    Wednesday, March 6, 2019 8:40 AM
  • It appears that you are attempting to use

    Expr1: "Signed this, the " & DayString(Day([GuarIssue])) & " day of " & MonthName(Month([GuarIssue])) & " in the Year " & Year([GuarIssue]) & "."

    as a query expression. If you want the message to be part of a report, then you shoud insert the message in a textbox control in the report rather than in a query. So insert a textbox in your report and use:

    = "Signed this, the " & DayString(Day([GuarIssue])) & " day of " & MonthName(Month([GuarIssue])) & " in the Year " & Year([GuarIssue]) & "."

    in the textbox Control Source property. Make sure the [GuarIssure] field is also a bound control in the report. The [GuarIssure] field should be formatted as a Short Date (mm/dd/yyyy) field. I don't know how or where users are inputting the GuarIssure field, but I assume it is input on a form. Is that correct?

    Thursday, March 7, 2019 5:03 PM