Syntax for previous month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to return data in which the FundedDate is equal to the previous
month. Here is what I have but it returns an error message
------------------------------------------------------------------------------------------------
Function SQLTest()

Dim strSQL As String
Dim strWhere As String

strSQL = "SELECT
[LoanNum],[B1LastName],[LO],[CloserName],[Investor],[Type],[Purpose],[PropType],[Occupancy],[Product],[TotalLoanAmt],[FundedDate],[LienPosition],[OrigFee],[DiscPts],[RebateAmt],[Gain/Loss],[LeadName],[LOEmail]
FROM [Fundings - SQL Test (Detail)]"
strWhere = DateSerial(Month([FundedDate]), -1)
DoCmd.OpenReport "Fundings by LO - SQL Test", acViewPreview, "", strWhere

End Functio
 
Ken

WHERE ?WHAT? = value to test

By the way, check Access HELP for the proper syntax on the DateSerial()
function -- you are missing a term. You may only need to use the Month()
function.
 
Jeff,

Value to test is [FundedDate]. I tried using better DateSerial syntax and
the report opens but the data is not filtering based on the dateserial
criteria (month - 1).

Jeff Boyce said:
Ken

WHERE ?WHAT? = value to test

By the way, check Access HELP for the proper syntax on the DateSerial()
function -- you are missing a term. You may only need to use the Month()
function.

--
Good luck

Jeff Boyce
<Access MVP>

Ken D. said:
I am trying to return data in which the FundedDate is equal to the previous
month. Here is what I have but it returns an error message.
-------------------------------------------------------------------------- ----------------------
Function SQLTest()

Dim strSQL As String
Dim strWhere As String

strSQL = "SELECT
[LoanNum],[B1LastName],[LO],[CloserName],[Investor],[Type],[Purpose],[PropTy
pe],[Occupancy],[Product],[TotalLoanAmt],[FundedDate],[LienPosition],[OrigFe
e],[DiscPts],[RebateAmt],[Gain/Loss],[LeadName],[LOEmail]
FROM [Fundings - SQL Test (Detail)]"
strWhere = DateSerial(Month([FundedDate]), -1)
DoCmd.OpenReport "Fundings by LO - SQL Test", acViewPreview, "", strWhere

End Function
-------------------------------------------------------------------------- ----------------------
Any help would be great...
Click to expand...
Click to expand...
 
Do you mean that FundedDate would be the prior month as in if this is
FEBRUARY, the FundedDate would be JANURARY -OR- Do you mean that the
FundedDate is 30 days prior to today's date? There's a REALLY big
difference.

David H
 
The DateSerial function required 3 values: a year, and month and a day, in
that order.

You could use DateSerial(Year([FundedDate]), Month([FundedDate]) -1,
Day([FundedDate])), but better would probably be DateAdd("m", -1,
[FundedDate])

However, that's only a part of your problem.

The Where condition in the OpenReport method needs to be a Where clause
(without the word Where). That means that you need to include a field to
which you're comparing the value.

I have no idea why you're including that SQL statement.
 
Try this for your Where clause (note that it is NOT all enclosed in quotes):

"Month([FundedDate])= " & Month(DateSerial(Year(Date), Month(Date), 0))

It relies on the fact that day 0 of the current month is interpreted as the
last day of the prior month (so January 0 = December 31).

--
George Nicholson

Remove 'Junk' from return address.


Ken D. said:
Jeff,

Value to test is [FundedDate]. I tried using better DateSerial syntax and
the report opens but the data is not filtering based on the dateserial
criteria (month - 1).

Jeff Boyce said:
Ken

WHERE ?WHAT? = value to test

By the way, check Access HELP for the proper syntax on the DateSerial()
function -- you are missing a term. You may only need to use the Month()
function.

--
Good luck

Jeff Boyce
<Access MVP>

Ken D. said:
I am trying to return data in which the FundedDate is equal to the previous
month. Here is what I have but it returns an error message.
-------------------------------------------------------------------------- ----------------------
Function SQLTest()

Dim strSQL As String
Dim strWhere As String

strSQL = "SELECT
[LoanNum],[B1LastName],[LO],[CloserName],[Investor],[Type],[Purpose],[PropTy
pe],[Occupancy],[Product],[TotalLoanAmt],[FundedDate],[LienPosition],[OrigFe
e],[DiscPts],[RebateAmt],[Gain/Loss],[LeadName],[LOEmail]
FROM [Fundings - SQL Test (Detail)]"
strWhere = DateSerial(Month([FundedDate]), -1)
DoCmd.OpenReport "Fundings by LO - SQL Test", acViewPreview, "",
strWhere

End Function
-------------------------------------------------------------------------- ----------------------
Any help would be great...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top