Issue with DateAdd function

[Dave14867]

Hello all,

VBA is not my strong suit yet and I am having yet another issue with it. I am trying to use the DateAdd function to take a date and add days to it based ion data in a table. I cannot seem to get the syntax correct in the function. I tried using the builder then copying that and placing it in the after update event and it doesn't like it.

Can someone let me know how to correct it.

If you open up the frmAnimalDataEntry-tabbed, go to the 4th record, select the "Breeding" tab, then update the "Date Bred" field it will cause the problem.



Pets-13.zip

Thanks

Dave

[Micron]

You can't just throw in a [table].[field] reference and expect Access to resolve it from within some other function. Even if it could, which record would you have it pick? And what if the field contained no value for the selected record? You need to explain what you want to do if you need further help. Otherwise, Dim a variable, assign it via DLookup, deal with the possibility of null, and pass the value as part of your expression. Maybe even better if you do the math before the DateAdd.

[June7]

Calculation should be in ControlSource, not DefaultValue. DefaultValue serves no purpose because it only works for new record and a new record would not have DateBred and Early values.


Can't reference a table directly like that. Access has no idea which record you need. Options in order of preference to retrieve species info:

1. Include Early field as a column in Species combobox on main form then textbox expression references that column.

=DateAdd("d", [Forms]![frmAnimalDataEntry-Tabbed].[Species].Column(3), [DateBred])

That will show an error if DateBred has no value. Day is default unit for arithmetic with dates, don't really need DateAdd:

=[DateBred]+[Forms]![frmAnimalDataEntry-Tabbed].[Species].[Column](3)

2. DLookup() domain aggregate

3. Include 'lookup' tables in form RecordSource


Really no need to save this calculated value, calculate when needed.

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

Recommend naming controls different from fields or other objects, such as cbxSpecies. I always name subform container control different from the object it holds, like ctrBreeding.

[CJ_London]

so to be clear the 'problem' is the code in the datebred after update event which is

Me.EarlyDate = DateAdd("d", [DateBred] + [tblSpecies]![Early], [DateBred])

I'm not going to spend a lot of time trying to decode your variables or what you are trying to acheive

by now you should know that dates are stored as numbers - today is 43582 for example

so if datebred is today, you are adding 43582 days to it - plus whatever [tblSpecies]![Early] is

I don't know where you are getting [tblSpecies]![Early] from but for it to work, you would need a recordset called tblSpecies which is in scope at the time the function is run

You also have the early field with a control source of your formula - so the afterupdate code is not required and should be removed.

[Dave14867]

June7,

I can't seem to get the DLookup correct. Can you take a look at it. I am close I believe but just not quite there. Go through the same staep as previous and when you change the Date Bred date the error will occur.

Pets-14.zip

Thanks

Dave

[June7]

Expression in textbox. Eliminate the VBA.

=[DateBred]+DLookUp("Early","tblSpecies","SpeciesID_PK=" & [Forms]![frmAnimalDataEntry-Tabbed].[Species])

[Dave14867]

June7,

Should that go in the default value of the DateBred or the default value of the unbound EarlyDate, or somewhere else?

Dave

[June7]

As advised in post 3, in EarlyDate textbox ControlSource property.

[Dave14867]

I got this figured out using a different method.

Thanks for everyone's help.