DATE filter using MS Access Database

[lemodizon]

Hi Everyone,

I'm trying to create an application that will display the records by entering the two DATES

but i'm having an error and it won't display the records.

I don't know if the DATE format is the problem or my code.

Code: Select all

on mouseUp
   DBConn
   SearchDates
end mouseUp

local tDatabaseID,sDatabaseID
local tSQL

command setDatabaseID pDatabaseID
   put pDatabaseID into sDatabaseID
end setDatabaseID


function getDatabaseID
   return sDatabaseID
end getDatabaseID

command DBConn
   put revOpenDatabase("ODBC", "Wbox", "TempDB",, )into tDatabaseID
   setDatabaseID tDatabaseID
   
end DBConn


Command SearchDates
   put getDatabaseID() into tDatabaseID
   put Field "Start Date" into tStartDate
   put Field "End Date" into tEndDate
   put "SELECT * FROM EODattr1 WHERE ACDate BETWEEN '"&tStartDate&"' AND '"&tEndDate&"';" into tSQL 
   put revDataFromQuery(tab,return,tDatabaseID,tSQL) into tList 
   set the dgData of group "dgEOD" to empty 
   
   repeat with x=1 to the number of lines of tList
      set the itemdelimiter to tab
      put item 1 of line x of tList into theDataA["A"]
      put item 2 of line x of tList into theDataA["B"]
      put item 3 of line x of tList into theDataA["C"]
      put item 4 of line x of tList into theDataA["D"]
      dispatch "AddData" to group "dgEOD" with theDataA,x
   end repeat
end SearchDates

hope you can help me

[Klaus]

Hi Lemon,

do you get any error dialog or just an empty datagrid?

Just chcked the internet for the date format of MS Access:
------------------------------------------------------------------------------------
...
Access automatically displays the date and time in the General Date and Long Time formats. The dates appear as, mm/dd/yyyy in the U.S. and as, dd/mm/yyyy outside the U.S. where mm is the month, dd is the day, and yyyy is the year. The time is displayed as, hh:mm:ss AM/PM, where hh is the hour, mm is minutes, and ss is seconds.
...
------------------------------------------------------------------------------------
You are outside of the USA, right?
So change your dates to the above mentioned format and try again: dd/mm/yyyy


Best

Klaus

[lemodizon]

Hi Lemon,

do you get any error dialog or just an empty datagrid?

Just chcked the internet for the date format of MS Access:
------------------------------------------------------------------------------------
...
Access automatically displays the date and time in the General Date and Long Time formats. The dates appear as, mm/dd/yyyy in the U.S. and as, dd/mm/yyyy outside the U.S. where mm is the month, dd is the day, and yyyy is the year. The time is displayed as, hh:mm:ss AM/PM, where hh is the hour, mm is minutes, and ss is seconds.
...
------------------------------------------------------------------------------------
You are outside of the USA, right?
So change your dates to the above mentioned format and try again: dd/mm/yyyy


Best

Klaus

Hi Klaus,

do you get any error dialog or just an empty datagrid?
I got empty datagrid



You are outside of the USA, right?
So change your dates to the above mentioned format and try again: dd/mm/yyyy

yes, i'm outside u.s i already changed the format the date on my windows setting still i got empty datagrid


see the below image date format i already changed

this is my file

TESTDB_ACCESS.rar

(6.02 KiB) Downloaded 205 times

hope you can help me guys thanks everyone

[Klaus]

Hm, I have no idea about MS Access, do you have a MS Access management app,
where you could try the SQL command? Know what I mean?

[lemodizon]

Hm, I have no idea about MS Access, do you have a MS Access management app,
where you could try the SQL command? Know what I mean?

Yes, I saw some old post here but I don't if it works
viewtopic.php?t=6173

i'm not sure if this is the cause of the problem

http://msdn.microsoft.com/en-us/library ... 85%29.aspx

Date values must be either delimited according to the ODBC canonical date format or delimited by the datetime delimiter ("#"). Otherwise, Microsoft Access will treat the value as an arithmetic expression and will not raise a warning or error.

For example, the date "March 5, 1996" must be represented as {d '1996-03-05'} or #03/05/1996#; otherwise, if only 03/05/1993 is submitted, Microsoft Access will evaluate this as 3 divided by 5 divided by 1996. This value rounds up to the integer 0, and since the zero day maps to 1899-12-31, this is the date used.

A pipe character (|) cannot be used in a date value, even if enclosed in back quotes.

I don't know on how format the date that ms access read it.

[Klaus]

Try this:

Code: Select all

...
put fld "startdate" into tStart
set itemdel to "/"
## make a repeat loop and go through the first two items and add a leading ZERO if neccessary
## 28/1/2024 -> 28/01/2024
## Now:
put "#" & tStart & "#" into tStartdateForMCAccess
### Same for the Enddate.
...

[lemodizon]

Hi Klaus,

Your code works and I removed the the single quote in tSQL coz MS access will read the single quote.

thank you very much

Code: Select all

Command SearchDates
   put getDatabaseID() into tDatabaseID
   put Field "Start Date" into tStart
   set itemdel to "/"
   put "#" & tStart & "#" into tStartdateForMCAccess  // MS access will read this format
   
   put Field "End Date" into tEndDate
   set itemdel to "/"
   put "#" & tEndDate & "#" into tEnddateForMCAccess // MS access will read this format
   
   put "SELECT * FROM EODattr1 WHERE ACDate BETWEEN "&tStartdateForMCAccess&" AND "&tEnddateForMCAccess&" " into tSQL  /// I removed the single quote inside of the variable
   
   put revDataFromQuery(tab,return,tDatabaseID,tSQL) into tList 
   
   set the dgData of group "dgEOD" to empty 
   
   repeat with x=1 to the number of lines of tList
      set the itemdelimiter to tab
      put item 1 of line x of tList into theDataA["A"]
      delete word 2 of  theDataA["A"] // This will remove the time i don't want to display it my datagrid
      put item 2 of line x of tList into theDataA["B"]
      put item 3 of line x of tList into theDataA["C"]
      put item 4 of line x of tList into theDataA["D"]
      dispatch "AddData" to group "dgEOD" with theDataA,x
   end repeat
end SearchDates

here is the output

[Klaus]

My pleasure!