error with the <= in dates

[megatronixs]

Hi all,
I'm going nuts on this one. I use a query to get a balance ledger that is less or equal to the day passed and only interested in the TOP 1 ORDER BY set to DESC.

SELECT TOP 1 BALANCE_LED
FROM CUSTACCT
WHERE ACCOUNT_MVT_DATE <=#2/1/2015#
ANS BRANCH_NO =12345
AND ACCOUNT =123456789
ORDER BY ACCOUNT_MVT_DATE DESC;

The above code will give me 3 results instead of 1 (all are the same actually)
After spending almost 2 hours looking in the net, I can't find nothing :-(

where do I go wrong?

Greetings.

 

[arnelgp]

Is that a typo mistake ANS. Remove "top 1" it will return only 1 record.

 

[megatronixs]

yes, it is a silly typo in the code I paste it here.
the regular code I use, is without the typo.

Greetings.

 

[megatronixs]

Hi Arnelgp,

If I remove the "top 1" I get all the records, like 30 something rows.
I only need to get the top one to paste it to a excel sheet.

Greetings.

 

[Minty]

Try adding SELECT DISTINCT (it's the Unique records option in the query designer). Can you double check that your query is as you have typed.

Cut and paste it instead of typing it up.

 

[megatronixs]

Hi Minty,

Using DISTINCT will show me still to many records as there are other dates to.
I corrected the ANS to AND, this should not give me errors.
I guess that access can't handle the <= in a date.

Greetings.

 

[Minty]

It definitely can handle <= , so there must be something else, is your date stored as a date?
Can you post up some sample data?

 

[megatronixs]

one detail after I added to show the date too, it passes the wrong date format to it:
the 20/01/2015 will come out instead all of the <=#2/1/2015#
the date in the database I query (Teradata) is set as 2015.01.02
maybe that could be the trouble. Maybe passing it with formatting it will work.

Greetings.

 

[arnelgp]

Select top 1 balance_led from (select balance_led from
from custacct
where account_mvt_date <=#2/1/2015#
ans branch_no =12345
and account =123456789
order by account_mvt_date desc)

 

[Galaxiom]

You are getting multiple records despite using TOP 1 because more than one record is in a tie for first in the order by. That is there are two records on the same day.

If you want one record you must use an ordering that picks a clear winner.

 

[isladogs]

one detail after I added to show the date too, it passes the wrong date format to it:
the 20/01/2015 will come out instead all of the <=#2/1/2015#
the date in the database I query (Teradata) is set as 2015.01.02
maybe that could be the trouble. Maybe passing it with formatting it will work.

Greetings.

Access is reading the date value as 1 Feb 2015 so 20 Jan fits the criteria

 

[megatronixs]

hi, that one worked out
but, the date I still get as 20/01/2015, any clue about that?

Greetings.

 

[arnelgp]

It's on your computer Regional setting.