VBA Query with Dates Question

[Huey462]

I’m trying to have the code look up the value of orders placed within a specified timeframe but cannot figure out how to format the formula. I’m sure it is a “simple” formatting issue but I’m way out of my depth here and don’t know what I’m looking for.

The error is: The expression you entered as a query parameter produced this error: ‘CustomStartV’

The original formula (abbreviated below) went from 1 Jan XX to 31 Dec XX (different XX for each year)

'Declares Variables
    Dim Total2003c As Variant
'Assigns values to variables
    TotalVal = DSum("[TotalPrice]", "[SupplyLog]")
    Total2003c = DSum("[TotalPrice]", "Supplylog", "[Date Ordered] > #1/1/2003# And [Date Ordered] < #1/1/2004#")
        If IsNumeric(Total2003c) = True Then
        'do nothing, it is a number
        Else: Total2003c = 0
        End If
'Assigns values to displayed form
    Me.Total2003 = Total2003c

The custom range is giving me an xxxxxxxx error.

Sub CustomRange()
    Dim TotalCustom As Variant
    Dim CustomStartV As Date
    Dim CustomEndV As Date
'*** Checks to see that a date is entered in both fields ***
    If Me.txtCustStart = "" Or Me.txtCustEnd = "" Then
       End
       Else
       'do nothing
    End If
'*** Checks to ensure start date is before end date and switches values if necessary ***
    If Me.txtCustStart.Value > Me.txtCustEnd.Value Then
        CustomStartV = Me.txtCustStart.Value
        CustomEndV = Me.txtCustEnd.Value
        Me.txtCustStart = CustomEndV
        Me.txtCustEnd = CustomStartV
    End If
'*** Looks up value of assets ordered between timeframe specified ***
    TotalCustom = DSum("[TotalPrice]", "Supplylog", "[Date Ordered] > CustomStartV And [Date Ordered] < CustomEndV")
        If IsNumeric(TotalCustom) = True Then
        'do nothing, it is a number
        Else: TotalCustom = 0
        End If
'*** Places resulting value on screen for user to see ***
    Me.TotalCust = TotalCustom
End Sub

As always, thank you in advance

 

[ranman256]

you dont need to use VB, that is the hard way.
Queries can do this without vb.
Use a form to set your date range, then the query will read that as part of the criteria.

 

[Huey462]

While you make a solid argument, in my line of work we aren’t allowed to work smarter, just harder. Seriously though I’ll tinker around with that option

 

[james_lankford]

change
TotalCustom = DSum("[TotalPrice]", "Supplylog", "[Date Ordered] > CustomStartV And [Date Ordered] < CustomEndV")

to this ( I think I have the quotes right)
TotalCustom = DSum("[TotalPrice]", "Supplylog", "[Date Ordered] > #" & CustomStartV & "# And [Date Ordered] < #" & CustomEndV & "# ")

 

[Huey462]

This is behaving very weird. The first time I ran the macro it came up with just over 2 million, which is close to what I was expecting. However every time after that it keeps coming up with nothing. I removed the checking for null and ensuring the start and end dates are valid portion to make sure they were not mucking up the data somehow and because I'm the only one with this version I have the two dates pre-set with valid values.

I wonder if it is how the dates are being stored that is causing an issue. Tried changing the "as Date" portion to variant and different number styles and it (as expected) created errors. That tells me that the code below is working because there is no error messages...now to just get it to work correctly(?).

Private Sub btnCalculate_Click()
    'Call CustomRange
    Dim TotalCustom As Variant
    Dim CustomStartV As Date
    Dim CustomEndV As Date
    TotalCustom = DSum("[TotalPrice]", "Supplylog", "[Date Ordered] > #" & CustomStartV & "# And [Date Ordered] < #" & CustomEndV & "# ")
    Me.TotalCust = TotalCustom
End Sub

 

[Micron]

[The custom range is giving me an xxxxxxxx error.]
Did I miss what the error is, or is it the 3rd line of your first post? If not, we're just guessing.
Here's my guesses:
- You declare variables as variants then seem to use them as if they were numbers. Or is '0' a default string value? Variants cannot be fixed-length strings.
- You declare the variables CustomStartV and CustomEndV but don't seem to assign them any values.
- For you to suggest it worked the first time and not since suggests you changed table data or something else.

 

[Huey462]

Ugh...thank you for pointing out the painfully obvious, I never assigned a value to the start and end date variables. Going to go hang my head in shame and think about what I did.