Unbound text boxes and dates syntax problems with vba

[Jimmyshoo]

Hope someone can point out the syntax problem which triggers error 13 data mismatch problem

DtStartDate = [regidate] >= # " & Me.Txt_Startdate & "#"

DimDtstartDate as date
The text box is on a form is unbound and format is set to shortdate

Hope you can help

 

[arnelgp]

what are you trying to do with dtStartDate, and what is [regidate], from which table?

 

[Jimmyshoo]

What i am trying to do is create code so for example you can see how many orders or in this case registrations were made over any given period. I have therefore a customer combobox and a start date and end date text box.i was going to just reference the form fields in the query but it caused too many problems when adding the customer field to the grid, so i found some similar code on youtube and thought i would customise it. The original code did not deal with dates.
Ultimately the values collected will be stored in a criteria variable and act as the where clause of a select count sql string
The table regidate comes from is icereporttable

 

[arnelgp]

your code currently is not returning a date but a Boolean (yes/no, true/false).
since you define DtStartDate as date, so you get the error.

if you're goal on the code is to get the date [regisdate] from table icereporttable, which is >= Me.Txt_Startdate:

DtStartDate = Dlookup("[regisdate]","icereporttable", "[regisdate] >=#" & Format(Me.Txt_Startdate, "mm\/dd\/yyyy") & "#").

if that is not the case, please explain further.

 

[Jimmyshoo]

Hi
Sorry i am not explaining myself clearly
I am creating a search form to collect criteria for a filter.
To simplify i could do with northwind database
If i wanted to create a search form to return (count) the number of orders between two dates and then also be able to break down by customer as well
So i want to know how many orders between 10th august and 17th august then further drill down further by customer.
The code i found that i thought i could use was by austin on youtube. He collects values from unbound form fields and uses them in the where clause of an sql statement. He then applies that to variable and finally sets this as the recordsource of the form. However his example does not use date fields and is not counting records it is a simple select query. My form will simple return a number. So 226 orders between these dates

 

[arnelgp]

assuming that on your form you have:

Me.Txt_StartDate (starting date)
Me.Txt_EndDate ( ending date)

first query will show many orders between those two textboxes:

select count(*) as NumberOfOrders From icereporttable 
   where [regisdate] 
   between Format([Forms]![yourFormName]!Txt_StartDate, "\#mm\/dd\/yyyy\#") And
   Format([Forms]![yourFormName]!Txt_EndDate, "\#mm\/dd\/yyyy\#");

the next sql will bring only those customers that has order between those dates:

select * [icereporttable] Where [regisdate]
   where [regisdate] 
   between Format([Forms]![yourFormName]!Txt_StartDate, "\#mm\/dd\/yyyy\#") And
   Format([Forms]![yourFormName]!Txt_EndDate, "\#mm\/dd\/yyyy\#") 
   order by [customerID], [regisdate];

the following sql will bring customer and Count of how many orders:

select [customerID], [customerName], Count(*) As NumberOfOrders
   from  [icereporttable] Where [regisdate]
   where [regisdate] 
   between Format([Forms]![yourFormName]!Txt_StartDate, "\#mm\/dd\/yyyy\#") And
   Format([Forms]![yourFormName]!Txt_EndDate, "\#mm\/dd\/yyyy\#") 
   group by [customerID], [customerName];

 

[Jimmyshoo]

Hello
Again thanks very much for the time you are spending on this. I very much appreciate it. The first piece of code looks like what i need but i need to reference the cbocustomer control on the form as well unless i am not reading this correctly the code does not seem to reference this?

 

[Jimmyshoo]

I tried the first code because its simply a summary query i am trying to run. It says the code is too complex to be evaluated. I tried to write it 3 times.
Before posting today i knew how to get a count referencing the form controls in the query grid. However when i added another field to the grid and tried to reference that one too it stopped working. Because i was struggling i looked for an alternative hence my first post.

 

[JHB]

Could you post your database with some sample data, (zip it because you haven't post 10 post), + name of the form?

 

[Jimmyshoo]

What i am looking for is code that adds up orders between two dates which the user enters on a form. So between 1st august and 10th august there are ten orders. Then i what the option of them choosing from a combo box a customer. So now i want to see the number of orders between 1st and 10th of august that were made by the customer that the user selects in the combo box. For example between 1st august and 10th august where the customer is walmart there were 2 orders. Its just a summary query for mi purposes