Access records via Excel VBa between dates

[CuriousGeorg]

Hi,

I need to be able to pull records back from an Access Database between two dates. However, the dates are not standard and would be entered by a user from a Userform.

how would I code this query . I am trying [

"SELECT * FROM Claims WHERE [NowDate] Between SDate('01/Jan/2013', 'dd/mmm/yyyy') And Edate('01/Jan/2020', dd/mmm/yyyy')"

where Sdate would be the Sdate text box in the UF and Edate is the End date in the UF.
E and S date are defined as:
SDate = Format(report.reportstart.Text, "dd/mmm/yyyy")EDate = Format(report.reportend.Text, "dd/mmm/yyyy")
Above.


Any ideas?

[Bob Phillips]

I would have thought something like

"SELECT * FROM Claims WHERE [NowDate] Between '" & Format(SDate.Text, "yyyy/mm/dd") & "' And '" & Format(EDate.Text, "yyyy/mm/dd") & "'"

[CuriousGeorg]

BINGO!..


just moved my do until!!!

Thanks XLD i guess access prefers the US date format no matter how much I try pull back UK times

[CuriousGeorg]

although, i do now have the problem that the records store in UK format and wont pull back on the report unless stored in US format..

if i swap things aroudn i get "object required"

actually scrap that.. it runs a report... but pulls nothing back!

there are no error's and there is just pulling nothing


Test.xlsm

there is a lot of code thats redundant in there and It just a play around file.. BUT.. if you can help me pull back the data in the report then please help..

(you'd obviously have to make your own ACCDB to test it!)

[Bob Phillips]

I can't test it Curious without a database to connect to. Can you post that as well.

BTW, I absolutely hate apps that won't allow you to click the X button, and throw up a message saying click close. It so easy to handle it automatically

Private Sub UserForm_QueryClose _
  (Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Call CommandButton1_Click
    End If
End Sub

[Bob Phillips]

BINGO!..


just moved my do until!!!

Thanks XLD i guess access prefers the US date format no matter how much I try pull back UK times

That is not a US date format, that would be mm/dd/yyy, it is just an unambiguous format, even Access can't mistake that date.

[CuriousGeorg]

ah apologies that wasn't meant to be in there.. hence some random code..

the database is quite simple .. I dont have access to it here its on a drive on a network that is down at the moment. It only has like 4 fields Handler, ClaimNumber, Status, NowDate, NowTime

put the entries in using the userform so i knew that aspect worked.. as far as you can see on that code though.. should there be anything not working?

worryingly the button to pull back everything works.. something in the dates doesnt appear to be right.. cant figure what..

(hate not having MS access at home!!)

[CuriousGeorg]

just looking at it .. it does appear to be the date "thing" thats wrong...

if i select 1st jan 2014 to 31st jan 2014.. nothing pulls through..
but if i put 1st jan 2013 to 31st jan 2014.. it pulls through both records even though the records were added in Feb

fyi it wont let me upload a DB..

[CuriousGeorg]

Could the problem lie in how Acess DB is set up? for example.. if i set the fields to TEXT format then the query runs but the reporting is wrong.
If i change to DATE then I get a mismatch error?

[Bob Phillips]

It's hard to say Curious. the problem with me building my database and testing it is that I would do things I usually do, and I know I can pull back dates okay in that scenario. So we need your database. Can you post it on a fileshare page, or in DropBox or OneDrive and share it publicly?

[CuriousGeorg]

thanks for your assistance XLD. After a long weekend I managed to figure it out.. had to swap ' for # and make sure the ACCDB was in date format.

[Bob Phillips]

I use .mdb, and I was sure I used '. Have to check that

[CuriousGeorg]

who knows.. but thanks for your help..

just 1 more thing for me to figure out and my database will be worked out..

stupid datediff :P