Date Function in microsoft access 2000 and up

[HGoodloe]

I'm working on a project in microsoft access 2000. I'm having an issue with Start and End Date manipulations. I'm trying to program the VBA code to display a message box if an incorrect Start and/or EndDate has been entered in the text boxes.

Is there a way to do such a thing in access using VBA?

 

[missinglinq]

Sure, you can do validation in either the control's or the form's BeforeUpdate event, but it would help us to help you if you were to tell us what constitutes an "incorrect" date!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

How are ya HGoodloe . . .

Have a look at the [blue]IsDate[/blue] function!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[HGoodloe]

Sorry I wasn't very specific. I'm working on a monthly records prepping database that has date_prepped as one of the fields. Dates for the current month, previous months and previoius year(s) are already in the table.

I guess what I didn't mention previously was that I would like to do a record search by a specific date that's already stored in the database.

If that particular date is not in the database or table, a message box should appear indicating that an incorrect date has been entered. I was trying do this using VBA code, unless there's a more easier efficient way of going aboutit.

This would probably be the equivalent of trying to search for a social security number that isn't in the database. If it's not in the database, then an error message would be prompt.

 

[JBinQLD]

Does this help?

Private Sub Command0_Click()
Dim x As Variant

x = DLookup("date_prepped", "tblYourTable", "date_prepped=#" & Me!dteYourFormField & "#")
If IsNull(x) Then 'no match found
    MsgBox ("No result found")
Else
    'Record found, your code
end if
End Sub

JB

 

[HGoodloe]

Thanks a lot your suggestion worked

 

[HGoodloe]

Hello once again, this is pretty much the same as what I sent last week in regards to searching a record(s) by date. I just realized that I failed to mention that the dates in which I was trying to use as a search are supposed to be Start Date and End Date as oppose to a single date.

The code that you provided did work for a single date setup, but it is actually needed for both Start and End Date

I did try unsuccessfully to manipulate the code you provided for the single date search, but no luck. So, here I am again.

So, the following is what I need for the Start and End Date set up. I have two text boxes on my form one for Start Date the other for end date. Can someone help me out with this. I'm kinda new at this so please excuse me for not being specific when I sent the request last week. THANKS.

I'm working on a monthly records prepping database that has date_prepped as one of the fields in the table. Dates for the current month, previous months and previoius year(s) are already in the table.

I need to do a record search by specific dates that already exist in the table.

If that particular date is not in the database or table, a message box should appear indicating that an incorrect date has been entered. I was trying do this using VBA code, unless there's a more easier efficient way of going aboutit.

This would probably be the equivalent of trying to search for a social security number that isn't in the database. If it's not in the database, then an error message would be prompt.

 

[JBinQLD]

Not entirely sure what you're after still. But I'd suggest the following pointers:

1) If you need to check two dates, simply try something like this:

Dim x As Variant

x = DLookup("date_prepped", "tblYourTable", "date_prepped=#" & Me!dteYourFormStart & "#")
If IsNull(x) Then 'no match found
    MsgBox ("No result found")
Else
    'Record found, lets check for other date
    x = DLookup("date_prepped", "tblYourTable", "date_prepped=#" & Me!dteYourFormEnd & "#")
    if isnull(x) then
         msgbox("Start date was good but no end date matched")
    else
         'woot - both date exist - your code
    endif

end if

2) If you're trying to work out whether a date exists in db between your start and finish, look at using the BETWEEN statement:

x=dlookup("field", "table", "DatePrepper BETWEEN #" & forms!yourForm!StartDate & "# AND #" & forms!YourForm!EndDate & "#;"

Hope that helps, if not maybe an example of data in fours tables and forms and some example results of what you'd like to achieve? Big luck.

JB

 

[TheAceMan1]

HGoodloe . . .

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] [blue]Its Worthy Reading![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[HGoodloe]

Ok, I believe the vba code you suggested is the right code, however I can't seem to get it to work. I spent all morning and much of the afternoon trying to get it to work.

The following is the actual code i'm using in the command button to perform my search based off the code you suggested. If the dates entered are correct and correspond with the dates already in the table, it should display the records associated with those dates.

If dates are incorrect then a message box is to appear indicating the error.

Dim x As Variant

x = DLookup("Date_Prepped", "Land_Acquisition", "StartDate BETWEEN #" & Forms!frm_Land_Acquisition!StartDate & "# and #" & Forms!frm_Land_Acquisition!EndDate & "#;")

If IsNull(x) Then 'no match found
MsgBox ("Date not in database")
Else
'Record found, your code
DoCmd.OpenForm "frm_Land_Acquisition", acFormDS, , "Date_Prepped=#" & Forms!frm_Land_Acquisition.StartDate & "# and #"

End If

Date_Prepped is the field in the table while StartDate and EndDate are the text boxes on the form where the dates will entered. The name of the form where the StartDate and EndDate text boxes are located is frm_Land_Acquisition.

The BETWEEN keyword should be included with the Start and End Date.

 

[PHV]

Perhaps this ?
x = DLookup("Date_Prepped", "Land_Acquisition", "Date_Prepped BETWEEN #" & Forms!frm_Land_Acquisition!StartDate & "# and #" & Forms!frm_Land_Acquisition!EndDate & "#;")


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JBinQLD]

The problem lies in your code:

DoCmd.OpenForm "frm_Land_Acquisition", acFormDS, , "Date_Prepped=#" & Forms!frm_Land_Acquisition.StartDate & "# and #"
[code]

This should be

[code]
DoCmd.OpenForm "frm_Land_Acquisition", acFormDS, , "Date_Prepped[red] between [/red]#" & Forms!frm_Land_Acquisition.StartDate & "# and #"[red]  & Forms!frm_Land_Acquisition!EndDate & "#")[/red]

JB

 

[HGoodloe]

Ok it did work this time. Everything is now working as expected. Thanks a lot!

Be blessed...