Dlookup not looking at the date value of field with default set to "Now()"

[smercer]

Hi all

I am having a small problem with a dlookup which is not looking at the date only (without the Datevalue function), but is looking at the entire field instead.

with the datevalue function it is giving errors "Data Type mismatch in criteria expression"

here is the code:

Total_Each_Book_Count = DCount("Book_Id", "tbl_Inventory_Each_Book", "DateValue(Date_Sold) = #" & Date & "#")

Can someone please help me? thanks in advance

 

[WayneRyan]

s,

Can't replicate your problem. I made a table, put some date/time entries
into it, and the DCount retrieved the appropriate ones.

Wayne

 

[smercer]

Wayne: Thanks for helping

I'll post entire code then

Me!sfrm_Sales_Sum.Requery


Total_Each_Book_Count = DCount("Book_Id", "tbl_Inventory_Each_Book", "DateValue(Date_Sold) = #" & Date & "#")

Total_Miscellaneous_Count = DCount("Miscellaneous_ID", "tbl_Sales_Miscell_Complete_Sold", "DateValue(Date_Sold) = #" & Date & "#")
MsgBox Total_Miscellaneous_Count

If Total_Each_Book_Count >= 1 And Total_Miscellaneous_Count = 0 Then
    Me.sfrm_Sales_Sum.Form.RecordSource = "qry_Control_Panel_Each_Book_Sales_Sum"
    Me.sfrm_Sales_Sum.Form!Grand_Total_Sales.ControlSource = "SumOfSale_Price"
    Me.sfrm_Sales_Sum.Requery
End If

If Total_Each_Book_Count = 0 And Total_Miscellaneous_Count >= 1 Then
    Me.sfrm_Sales_Sum.Form.RecordSource = "qry_Control_Panel_Miscellaneous_Sales_Sum"
    Me.sfrm_Sales_Sum.Form!Grand_Total_Sales.ControlSource = "Total_Miscelleanous_Sales"
    Me.sfrm_Sales_Sum.Requery
End If

If Total_Each_Book_Count >= 1 And Total_Miscellaneous_Count >= 1 Then
    Me.sfrm_Sales_Sum.Form.RecordSource = "qry_Control_Panel_Grand_total_of_all_sales"
    Me.sfrm_Sales_Sum.Requery
End If

If DCount("Total_Discount_Given", "tbl_Sales_Discounts_Given", "Discount_Date = #" & Date & "#") = 0 Then
    Me.sfrm_Sales_Sum.Form!txt_Total_Discounts.ControlSource = ""
    Me.sfrm_Sales_Sum.Form!txt_Total_Discounts.Visible = False
    
    Else
    If DCount("Total_Discount_Given", "tbl_Sales_Discounts_Given", "Discount_Date = #" & Date & "#") = 1 Then
    Me.sfrm_Sales_Sum.Form!txt_Total_Discounts.ControlSource = "=[Grand_Total_Sales]-sfrm_Control_Panel_Discounts_Given.Form!Total_Discount_Given"
    End If
End If
Miscell_Sold = Nz(DSum("Quantity", "tbl_Sales_Miscell_Complete_Sold", "DateValue(Sold_Date) = #" & Date & "#"), 0)

Me.sfrm_Sales_Sum.Form!txt_Quantity_Sold.Value = Total_Each_Book_Count + Miscell_Sold

Also I just noticed that I put in title "Dlookup" rather then "Dcount". Opps...

This is for suming the sales made for both Each_Book category and the miscellaneous books and then adding them both together to give a grand total.

I just had another look at the default values and the miscellaneous is just "Date()".

May I ask a stupid question? This is what is causing it isn't it?

 

[smercer]

Doing some more experimenting, just tried this and it is giveing an error "type mismatch"

Total_Each_Book_Count = DCount("Book_Id", "tbl_Inventory_Each_Book", "Date_Sold like #" & Date & "# & " * "")

Thanks for helping

 

[WayneRyan]

s,

On my way to work ...

"Date_Sold like #" & Date & "# & " * "")

If Date_Sold is really a date/time field, then the # are right.
But the like won't work with a date, you'd have to use format
to make it a string, then use Like.

The " *" out at the end syntactically makes no sense at all.
Will look in later.

Wayne

 

[Rich]

Why do you have more than one table for book sales?

 

[smercer]

Why do you have more than one table for book sales?

One table is for books that the user wants to type the details in eg title, author, etc

The other table is for books like magazines and novels (a series of westerns) that that the user is not wanting to enter as descriptive. this table uses a quantity field for a whole series in general instead of entering each book. (if your going to say that is a crappy idea, this is what the user has asked for)

I don't think I can get it any simpler (although complex) than that to set up, but at least it is better for the user.