Recordset Source

  • Thread starter Thread starter Heapy
  • Start date Start date
H

Heapy

I'm creating some recordsets where the source is an SQL statement.

For some reason, the OpenRecordset method is truncating the SQL Statement to
the first 256 characters of the variable that holds the statement (i.e. the
length of the string might be 600 characters, but only the first 256 make it
into the OpenRecordset).

Any ideas what I'm doing wrong.

e.g.

SQLBilling = "SELECT BILLING_TXNS.TXN_DATE, CONSULTANTS.CONS_NM,
BILLING_TXNS.UTIL_IND, BILLING_TXNS.CHARGE_IND, BILLING_TXNS.PROJECT_ID,
BILLING_TXNS.TOT_HOURS, BILLING_TXNS.BILLING_STATUS,
CONS_RATES.CONS_DEF_RATE, BILLING_TXNS.CONS_ACT_RATE"

SQLBilling = SQLBilling & " FROM (CONS_RATES INNER JOIN CONSULTANTS ON
CONS_RATES.CONS_LVL = CONSULTANTS.CONS_LVL) INNER JOIN BILLING_TXNS ON
CONSULTANTS.CONS_ID = BILLING_TXNS.CONS_ID"

SQLBilling = SQLBilling & " WHERE (((BILLING_TXNS.TXN_DATE)>=" & Date1 & "
And (BILLING_TXNS.TXN_DATE)<=" & Date2 & "));"

Set rstBil1 = CurrentDb.OpenRecordset(SQLBilling, dbOpenDynaset, dbReadOnly)


The SQLBilling variable is defined as a string, and does report the correct
SQL statement when printed, but only the first 256 characters make it into
the recordset.

Thx for any help,
HWH
 
For some reason, the OpenRecordset method is truncating the SQL
Statement to the first 256 characters of the variable that holds the
statement (i.e. the length of the string might be 600 characters, but
only the first 256 make it into the OpenRecordset).

Any ideas what I'm doing wrong.
Click to expand...

I'm using Access 2000 and this works fine:

strSQL = String$(1024, " ") & "SELECT * FROM Temp"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)

How are you declaring your SQLBilling string? There are different limits
for strings that were dimmed locally and globally and as parameters, but I
don't think there are any limits as low as 256, except, of course, for Text
table fields.
The SQLBilling variable is defined as a string, and does report
the correct SQL statement when printed, but only the first 256
characters make it into the recordset.
Click to expand...

How do you know this? I notice from the code, there is some extremely dodgy
parameter substitution going on, for example

"...(BILLING_TXNS.TXN_DATE)>=" & Date1 & "And ...

which means that Date1 should be a string suitably formatted and space-
padded, which of course it might be. I wonder if your command is actually
wrong because the SQL is malformed rather than truncated.

HTH


Tim F
 
Tim: thx for your help.

I was assuming only the first 256 characters were accepted because the
recordset's .Name property only contained the first 256 characters of the
SQL string (didn't know this - useful - won't be relying on that anymore).

You're correct - the problem was with the dates. Date1 and Date2 were
defined as dates, not strings.

Thx
HWH
-----------
 
You're correct - the problem was with the dates. Date1 and Date2 were
defined as dates, not strings.
Click to expand...

In that case you need something like

"...(TXN_DATE >= " & Format$(Date1, "\#yyyy\-mm\-dd\#") & ") And ..."

but you prolly knew that already :-)

B Wishes


Tim F
 
Back
Top