Myrna,
Yes, the -2, -1 actually were just my attemts at troubleshooting the code.
It did not work with or without it. It querys alright but does not roll back
to previous months. tripple checked my parenthasies but no clue as to why it
isnot worki


Thanks,

Patrick


"Myrna Larson" wrote:

If it is "almost" working, then it looks to me as if you've introduced a bug
in Jamie's code. For the first Iff function, since it's -Iff (rather than
+Iff) the 2 and 1 inside the parentheses should be positive, not negative. If
you make them negative you are adding 2 or 1 month instead of subtracting.

FWIW, When I was doing queries by passing the SQL string, I had to pass dates
in this format: #mm/dd/yyyy#, i.e. #09/27/2004#, with the pound signs. Passing
a date variable as such didn't work for me.

So my code (in part) looked like this:

Dim SQL As String

SQL = "SELECT PrTicker, PrNAV FROM Prices WHERE PrDate = DDD" & _
" ORDER BY PrTicker"
SQL = Replace(S, "DDD", SQLDate(TheDate))

The function to format the date correctly, SQLDate, looked like this:

Function SQLDate(ADate As Date) As String
SQLDate = Format$(ADate, "\#mm/dd/yyyy\#")
End Function

I like to write out the SQL statement using "place holders" for the variables,
then use the Replace function to insert the correct values. I would write your
code as

Dim sDate As String
Dim eDate AS String
Dim SQL As String

sDate = SQLDate(DateSerial(Year(Now()), _
Month(Now()-IIf(Day(Now())<16,2,1),16))
eDate = SQLDate(DateSerial(Year(Now()), _
Month(Now()-IIf(Day(Now())<16,1,0),15))

SQL = "SELECT * FROM tblHistory WHERE (tblHistory.Date_Updated) Between
SDATE and EDATE ORDER BY tblHistory.date_Updated DESC;"
SQL = Replace(SQL,"SDATE",sDate)
SQL = Replace(SQL,"EDATE",eDate)

Then open the record set with the SQL statement.


On Mon, 27 Sep 2004 09:29:02 -0700, "PSKelligan"
wrote:

Hello again,

I tried to add your sql to my query and it seemed to work ok except for the
-IIF portion of the statement. Here is my statement:

SELECT *
FROM tblHistory
WHERE (((tblHistory.date_Updated) Between
DateSerial(Year(Now()),Month(Now()-IIf(Day(Now())<16,-2,-1)),16) And
DateSerial(Year(Now()),Month(Now())-IIf(Day(Now())<16,1,0),15)))
ORDER BY tblHistory.date_Updated DESC;

Any syntax isues that are preventing this? Your little bit of sql, if it
will work in this statement will make all my problems go away.

Thanks,

Patrick