Page 1 of 1
DATE filter using MS Access Database
Posted: Fri Aug 23, 2024 3:48 pm
by 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
Re: DATE filter using MS Access Database
Posted: Fri Aug 23, 2024 4:03 pm
by 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
Re: DATE filter using MS Access Database
Posted: Sat Aug 24, 2024 9:42 am
by lemodizon
Klaus wrote: ↑Fri Aug 23, 2024 4:03 pm
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
hope you can help me guys thanks everyone
Re: DATE filter using MS Access Database
Posted: Sat Aug 24, 2024 9:46 am
by 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?
Re: DATE filter using MS Access Database
Posted: Sat Aug 24, 2024 10:21 am
by lemodizon
Klaus wrote: ↑Sat Aug 24, 2024 9:46 am
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.
Re: DATE filter using MS Access Database
Posted: Sat Aug 24, 2024 10:29 am
by 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.
...
Re: DATE filter using MS Access Database
Posted: Mon Aug 26, 2024 4:06 am
by 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
Re: DATE filter using MS Access Database
Posted: Mon Aug 26, 2024 8:22 am
by Klaus
My pleasure!
