Include all data in listbox to a report

[Hacking01]

Hi All, I'm new here as well as my knowledge about Microsoft access vba .

I have a form. In my form, I have two text box that will get the start date and the end date and a button that will query the data to my sql. Then the data will be shown in listbox. What I want is that the ALL the data in the listbox will be displayed in the report without selecting one by one. Is it possible?

please help :)

[gnawoncents]

Hi, Hacking, and welcome to Bytes! If I'm understanding correctly, the user will input a start and end date, and you want to generate a report of records between those dates. The easiest way, depending on your current setup, should be to use the dates to filter the report's source records as you open the report. Something like the following should get you started:

DoCmd.OpenReport "YourReportName", acViewPreview, , "fldDate BETWEEN #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"
 

Where
YourReportName = the name of the target report to filter and open
fldDate = the field in your source table that has the dates to search through
txtStartDate = the name of the textbox on the form used to enter the start date
txtEndDate = the name of the textbox on the form used to enter the end date

Of course this will only work if my assumptions about your current construct are correct. Otherwise, you can use this as a starting point or provide more detailed information, and we can help you work toward a better answer.

[NeoPa]

We get a lot of very helpful posts on here that are nevertheless careless of how properly to deal with dates in SQL (Literal DateTimes and Their Delimiters (#)). It works fine in the States but for the rest of the world it causes many problems which are very difficult to determine or debug. Dealing with date literals in SQL is most emphatically NOT about simply dropping the default display into a SQL string with hashes (#) around them.

Don't get me wrong. GnawOnCents' post is very helpful, and they're a very valued and appreciated member of our community. It's simply that not all members are aware of issues that occur only outside of the USA.

So, if you want to write reliable and portable code, or even simply happen to live outside of the USA, then you'd be well advised to ensure your date literals are handled more robustly.

@Hacking01
I see you're new. Welcome to Bytes.

Experience tells me that members get more help and sympathy when their posts are prepared with care, rather than thrown together in haste. It takes very little effort and doesn't require any technical knowledge.

[gnawoncents]

NeoPa,
Thank you for the warning. I read your linked article. Just to make sure I understand correctly--you're saying that the date format needs to be identified inside the command line? Would it be different depending on where one is in the world or only on how it is already stored in the database? I don't want to lead anyone astray and appreciate the insight!

[NeoPa]

I'll try to explain as clearly and simply as I can.

Dates are stored the same way regardless. This is not about how they're stored. It's about how they're displayed. The problem is that people use the default display format in order to build up SQL strings (or commands) in their code.

If you say strX = datVar then the VBA interpreter will see a string on one side and a Date on the other and will know the Date needs to be converted to a string, so it works out which characters to include in this sub-string using what it knows about your locale settings. In the USA it will select a format that is actually validly recognised by SQL as exactly the date you're thinking of. In much of the rest of the world it would not.

A SQL command string is not designed for human viewing. That's an important point. It's not designed for human viewing. SQL is a command interpreter and it does so following clearly defined and specific rules. Just dumping some digits and other stuff that look to a human like it may be a date is absolutely not good enough. It must follow a standard format otherwise it's garbage.

You'll understand that exactly the same is going on when you say :

strX = "... WHERE ([DateField] Between #" & datVar & "# And Date())"

It sees a Date value that needs to be converted to a string. It uses your locale settings. Its only concern is to produce characters in order that represent a date string to the local human populace. There is nothing there to ensure it matches SQL date requirements. In most of the world it won't.

Consider one of the worst case scenarios. It's the first day of August and you're running the application in the United Kingdom instead of USA. Instead of :

... WHERE ([DateField] Between #8/1/2018# And Date())

You get :

... WHERE ([DateField] Between #1/8/2018# And Date())

Why is that a worst-case scenario? Because it doesn't fail! Users are likely to think all is well yet it's just used a completely different date from the one intended. Your business figures have just gown down the pan because someone sloppily referred to 8th January instead of 1st August.

With :

... WHERE ([DateField] Between #2018-8-1# And Date())

That can never happen of course.

[gnawoncents]

NeoPa,
Thank you for the clarification. I will be more careful with dates and strings from here on out!

[NeoPa]

Good for you :-)

It's not a problem that's obvious to most developers.

[twinnyfo]

NeoPa,

Very informative and useful! Not sure I'll be doing much outside the USA, but this helps me understand my world a bit better.

Thanks!