Perform a BETWEEN 2 dates from a form query

[boblarson]

1. You don't need the Else.

2. The code should work. It should, if the To date is before the From date hit the Cancel = True which should cancel the update and the Me.txtDateTo.Undo should undo the value in the control. So, not sure why it isn't working.

3. You COULD try this instead.

Private Sub txtDateTo_BeforeUpdate(Cancel As Integer)
    If Me.txtDateTo.Value < Me.txtDateFrom.Value Then
        Cancel = True
        Me.txtDateTo = Null
        MsgBox "A date range must END later than it BEGINS!", vbCritical
    End If
 
End Sub

But it might not like the event trying to change the value in it.

 

[jonno_g]

1. You don't need the Else.

I know, but it's called 'force of habit', and it does no harm.

2. The code should work. It should, if the To date is before the From date hit the Cancel = True which should cancel the update and the Me.txtDateTo.Undo should undo the value in the control. So, not sure why it isn't working.

I agree entirely - unfortunately, it doesn't.

3. You COULD try this instead.

Private Sub txtDateTo_BeforeUpdate(Cancel As Integer)
    If Me.txtDateTo.Value < Me.txtDateFrom.Value Then
        Cancel = True
        Me.txtDateTo = Null
        MsgBox "A date range must END later than it BEGINS!", vbCritical
    End If
 
End Sub

But it might not like the event trying to change the value in it.

I've already tried the suggestion that you give, but with no luck. I've also tried setting the value of the field to a zero length string, but to no avail.

I've just now come up with a solution that works in this instance, but I'm not sure if it is the best solution for all occasions.

Anyway, here 'tis:

Private Sub txtDateFrom_AfterUpdate()
    Call fnDateCheck
End Sub
 
Private Sub txtDateTo_AfterUpdate()
    Call fnDateCheck
End Sub
 
Private Function fnDateCheck()
    If Me.txtDateTo.Value < Me.txtDateFrom.Value Then
        Me.txtDateTo.Value = ""
        MsgBox "A date range must END later than it BEGINS!", vbCritical
    Else
    End If
 
End Function

 

[j.smith1981]

I am starting to loose the plot with this extended SQL query I am trying to perfect in Access again, sorry this is as much of a repost of the same SQL query I asked you guys for.

But could you help me come up with a more suitable way of allowing the user to enter empty cells and query the table, so they dont have to enter any data aswell as restricting it by searching for fields?

Here's my current SQL, this is as perfect for now and I will revise it with the guy that wants this doing:

SELECT data.test_date, tech.id, tech.tech_name, supplier.id, supplier.supplier, cartridge_type.id, cartridge_type.type, cartridge.id, cartridge.manufacturer_id, manufacturer.manufacturer, cartridge.cartridge_code, printer.id, printer.printer, data.test_page, data.yield, data.paper_used, data.location
FROM tech INNER JOIN (supplier INNER JOIN ((manufacturer INNER JOIN (cartridge INNER JOIN (cartridge_type INNER JOIN data ON cartridge_type.id = data.cartridge_type_id) ON cartridge.id = data.cartridge_id) ON manufacturer.id = cartridge.manufacturer_id) INNER JOIN printer ON (printer.id = data.printer_id) AND (manufacturer.id = printer.manufacturer_id)) ON supplier.id = data.supplier_id) ON tech.id = data.tech_id
WHERE (

((data.test_date) Between [Forms]![query_form]![from_date_txt] And [Forms]![query_form]![to_date_txt]) AND ((tech.id)=[Forms]![query_form]![cmb_tech_id]) AND ((supplier.id)=[Forms]![query_form]![cmb_supplier]) AND ((cartridge_type.id)=[Forms]![query_form]![cmb_carttype]) AND ((cartridge.id)=[Forms]![query_form]![cmb_cartcode]) AND ((printer.id)=[Forms]![query_form]![cmb_printer])

);

Could I have some help with this its like a form is null then allow any data to be searched or like that.

Like is null then return all fields as per the other searched criteria, so to speak.

I look forward to any responses.

Thanks,
Jeremy.

 

[Brianwarnock]

could you help me come up with a more suitable way of allowing the user to enter empty cells and query the table, so they dont have to enter any data aswell as restricting it by searching for fields?

I don't understand this, its a long time since I looked at that link I posted earlier but I am 100% sure that the DB does what you are asking over many fields.
The approach of Where (Field = criteria or criteria is null) And (....

is simple and effective.

Brian

 

[j.smith1981]

Not to worry I've sorted it.

Sorry I didnt get back to you before you replied but yes it worked.

I do apologise about that though.

Kind regards,
Jeremy.