Odd question: SQL statements fail...

[AJames]

So after months of me bitching they are finally going to upgrade my
computer.. if we can figure out why most of my excel/access macros no
longer run..
how simple?

MOST but not all fail, Simple SQL statements that have worked for
years now return "No Current Record" but other quite complex
statements work fine..

this statement for example returns "No current record"

select2 = " SELECT * FROM mortgage_rate "
select2 = select2 & "WHERE date =#" & valdate & "#"


These statements stared life in NT4 and Access '97 and have been
through a bunch of software and hardware upgrades in the last 10
years.. and they still work on existing computers with XP and office
2003.

on the new PC with what looks to be identical software config they
fail..

When I say Identical I mean same version numbers for Excel / Access /
dao360, is there anything else I should be looking at?

Or could it be that the new hardware is just too darn fast? The
databases that are being touched are on a network and range in size
from 20 Meg to 1.4Gig .. (they have been compacted and repaired)

Any Ideas as to where I should be looking?

 

[Allen Browne]

You have not given use much to go on here, but there are several possible
issues.

1. Date is a reserved word, so can cause queries to fail. Ideally, rename
the field. At minimum, add square brackets and the table name.

2. Do you know if valdate is a valid date value? Could be Null? Or something
else?

3. The regional settings on the new computer might cause it to concatenate
the date value into the string differently. Explicitly format as expected by
JET.

So:
If IsDate(valdate) Then
select2 = "SELECT * FROM mortgage_rate WHERE mortgage_rate.[date] =
#" & Format(valdate, "mm\/dd\/yyyy") & "#;"
Debug.Print select2
Else
MsgBox "Supply a date."
End If

You mention DAO360, but don't say which version of Access you are working
with, nor which file format. You should not reference DAO360 if working with
an Access 97 format, nor if you are using the ACCDB format in A2007. More
info:
http://allenbrowne.com/ser-38.html#LibVer

If there really is a bad index, a compact/repair might help. If you have
switched version, a decompile might help. It's probably worth running
through the steps of a standard recovery sequence:
http://allenbrowne.com/recover.html

If you are using A2007, this might help:
http://allenbrowne.com/Access2007.html

For a list of reserved words to avoid when designing tables, refer to:
http://allenbrowne.com/AppIssueBadWord.html

 

[vseale]

Check the ODBC connection on the new PC.

 

[AJames]

You have not given use much to go on here, but there are several possible
issues.

3. The regional settings on the new computer might cause it to concatenate
the date value into the string differently. Explicitly format as expectedby
JET.

So:
    If IsDate(valdate) Then
        select2 = "SELECT * FROM mortgage_rate WHERE mortgage_rate.[date] =
#" & Format(valdate, "mm\/dd\/yyyy") & "#;"
        Debug.Print select2
    Else
        MsgBox "Supply a date."
    End If

That was it.. at least on the five I've tested so far..

many thanks ..