VB Date Range coding to return all or select from two fields

[sugarbunnie]

Am really new to using VB, working on an application where the between..and criteria for sorting date will not work for what has been spec'd out. Is it possible to build a sSqlWhere statement that will pull either a date range or return all data if fields are left blank?

 

[Darrylles]

Hi,

You need to be more specific.

Either pull all records within a date-range OR all records where all fields are blank.

Based on what?

How does Access KNOW which of these that you want?

Do you want records from the date-range, OR do you want all records that have blank fields?

Kind Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[PaulBricker]

If you are pulling the values from a Form then the syntax in your query date field criteria would be.

Between Forms!YourForm!StartDate and Forms!YourForms!EndDate Or Forms!YourForm!StartDate Is Null And Forms!YourForm!EndDate Is Null

If you are using parameters in your query then this should do it

Between [Enter Start Date] And [Enter End Date] Or [Enter Start Date] Is Null And [Enter End Date] Is Null

Access will rewrite this after you save the changes but it's easier to explain this way.

Paul

 

[sugarbunnie]

Hi Darrylle

I've written a main query that is posting to a _real copy and the table sources used for this query are linked tables from an oracle database. The vba statement that has been complied so far has three Where clauses that work very nicely for the variables in other fields which pull from combo boxes. The issue I cannot get resolved is getting two date fields to perform in a like manner. Either left blank to return all data including null fields or select a range of dates in the combo box that are being pulled directly from the two fields in the individual tables on the query generation form.

Thanks

MG

 

[sugarbunnie]

Thanks Paul, I will try that option too and see if that makes my client a happy camper. It would certainly make me one!

MG