date field equal to date field

[danflipper]

hi, guys

this is my first post on here! ive been using this forum for well over a year now and its been a god send! hope you can help me with this one, ive searched the forum and others like mad and just cannot find a solution.

i have an unbound form which is populated by another form's startdate and enddate. when these are populated the user can click a button which inserts all of the dates in between those start and end dates using this code -

Me.txtdate2 = Format(DateAdd("d", 1, Me.startdate))
Me.txtdate3 = Format(DateAdd("d", 2, Me.startdate))
Me.txtdate4 = Format(DateAdd("d", 3, Me.startdate))
ETC ETC for 14 days

now i need to check if the date in a txtbox is equal to another txtbox with a date on the form

i have tried this

If Format(DateAdd("d", 2, Me.startdate)) = Me.enddate11 Then
and this

If Me.enddate11 = Format(DateAdd("d", 2, Me.startdate)) Then

and this

If Me.enddate11 = me.txtdate3 Then

but have had no joy, ive been looking at this in different ways for about a week now and its doin my head in

could anyone please help

thanks in advance guys

 

[Guus2005]

you are using the format statement whithout a format

format(date(),"dd-mm-yyyy")

Perhaps this is the problem.

 

[danflipper]

sorry guus i dont understand what you mean!

the entering of the date is fine. it populates the txtbox's fine and with correct formatting

what i want is is the code to recognise that one of the txtboxes is the same as a date in a txtbox i specify e.g enddate

thanks for your reply

 

[danflipper]

can no one help me?

im on my last brain cells here! lol

i dont understand why i cant get access to recognise 1 date field as being the same as another date field? sounds like it should be simple to me! is it not?

 

[ted.martin]

Have you tried the DLOOKUP function?


Dim varX As Variant
varX = DLookup("[aDate]", "aTable", "[a Datefieldinyour aTable] = " #adatevalue#)
'
If IsNull(varX) Then
' Do Something
Exit Sub
Else
MsgBox "Duplicate Date"
Exit Sub
End If

 

[danflipper]

i have never used it before

how would i use it in this context?

thanks

 

[ted.martin]

at the point where you need to run

"i need to check if the date in a txtbox is equal to another txtbox with a date on the form"

run the DLOOKUp function by looking at the underlying table.

If this is by any chance a new record, then before to run the IF, it might be useful to save the record so that it is in the table.

Docmd.RunCommand accmdSaveRecord

 

[danflipper]

i am sorry to have wasted your time but i did say in my first post that this was an UNBOUND form and the entries in the txtboxes were generated by the code!

 

[ted.martin]

Yea - I missed that - have you tried making a (public) variable equal to the value in the unbound form? You will be able to interrogate the value then.

 

[danflipper]

sorry dont understand!

i am quite an amature to access code! where i can do quite a few things im lost with this one

 

[Brianwarnock]

I'm going to guess that txtdate2 etc are text fields and that startdate is a date field and never the twain shall meet.

Hmm just checked and noticed that its enddate11 so maybe same applies.
So make sure all of the controls on the form have format of date in their properties AND DON't use Format it creates a string.

Brian

 

[danflipper]

brainwornock,

thank you so much for the hint - how could i be so stupid to forget format creates a string, obviously been looking at this problem too long!

thank you for your time and help

 

[danflipper]

just to clarify folks,

Me.txtdate3 = DateAdd("d", 2, Me.startdate)
Me.txtdate4 = DateAdd("d", 3, Me.startdate)
Me.txtdate5 = DateAdd("d", 4, Me.startdate)

^^^^puts the dates in the boxes^^^^

and

If Me.txtdate3 = Me.enddate11 Then
'do something
End If

^^^^checks for the equal^^^^

finally i get what i want lol

thanks all

 

[Brianwarnock]

Glad to have helped, and I love my new first name

Brian

 

[danflipper]

ha ha sorry about the name!