Type mismatch with weekday function (1 Viewer)

[i4004]

Hi

I have a calendar table which has a date field, 'TeachDay' and a weekday field 'Weekday'.

I wish to write VBA code to add records for each new day and then to store the weekday (okay perhaps it should be calculated on the fly but I want to do this vba code).

I have succeeded in adding new days with the code below but only get a type mismatch with the weekday function part. What am I missing here?

Private Sub Command17_Click()
Dim TempDate As Date
Dim TempWeekday As Integer

'Adds new record to tblTeachDay
DoCmd.GoToRecord , , acNewRec

'Looks up highest existing date and adds one to it
TempDate = (DMax("[TeachDayDate]", "[tblTeachDay]")) + 1

'Makes the new date field in the table = to the calculated date
TeachDayDate = TempDate

'Finds the day of the week for the new date
TempWeekday = Weekday(TempDate) xxxxtype mismatch errorxxx

'Puts the date integer on the form for inspection
Me!txtTemp = TempWeekday

DoCmd.Beep

End Sub

 

[datAdrenaline]

So ... where is "TeachDayDate" declared? Is it a control on the form? If so, is it possible that you have a control on the form that is in conflict with the name of one of your variables? What is the value of TempDate prior to your code getting to that line?

Kinda grabbing at straws here as it appears there is does not appear to be anything syntactically incorrect with your code. Any chance of posting the db (with bogus data in it) and at least for Form in question?

 

[SOS]

So ... where is "TeachDayDate" declared? Is it a control on the form? If so, is it possible that you have a control on the form that is in conflict with the name of one of your variables? What is the value of TempDate prior to your code getting to that line?

Kinda grabbing at straws here as it appears there is does not appear to be anything syntactically incorrect with your code. Any chance of posting the db (with bogus data in it) and at least for Form in question?

It looks to me that the OP is trying to update the FIELD in the table (which of course they can't do it this way).

 

[datAdrenaline]

Just noticed this ..

>> ... and a weekday field 'Weekday'. <<

You'll want to change the name of that field.

 

[i4004]

Renamed that 'Weekday' field - that didn't fix it though!

tblTeachDay(TeachDayDate, WeekdayName, etc)

TeachDayGenerateNewYear Form with tblTeachDay as record source.

Controls on the form are the fields from the table, the command button to run the code and the inspection text box contro, txtTemp.

Just put txtWeekday, '=Weekday([TeachDayDate]) on the form as a check, this works fine, returning the integer value of the weekday.

However, I wish to run this in code, so any help appreciated.

 

[i4004]

Ah, I have just started afresh, new database file, new table, new form and the code worked.

I then tried the next stage, to use the WeekDayName function to return the string name of the weekday integer.

This stopped with a type mismatch error. Renamed my table field 'WeekDayName' (doh!) so that it didn't conflict with the function name and this still doesn't work.

It appears that once the function has been conflicted with a field that it bears a grudge and will not work again. Tried repair but to no avail. So I will start afresh without any conflicting field names.

 

[i4004]

Made a third version, with the TempWeekDayNumber as Long and it all worked.

Thank you for your help.

 

[datAdrenaline]

Your welcome! ... I wish you continued success! ...

As a side note with respect to:

>> It appears that once the function has been conflicted with a field that it bears a grudge and will not work again <<

Did you change the name of the Control that the field was bound to? For example: if you have a Field named "Weekday", then drag that field onto your Form, you get a Text Box control named "Weekday" with a Control Source property that references the Fields named "Weekday". Then when you change the Field name to "WeekdayX" and your Control Source to the new Field name of "WeekdayX", your Control Name is still named "Weekday", so you need to change the Text Box control name as well.

Many developers do rename all the controls as a standard practice. For example: In this case the new Text Box control was name "Weekday" by default, many developers would have automatically changed it to "txtWeekday" in order to help them in there coding so they can help distinguish what their code is refering to. I personally don't practice that, but I wanted to hilite the objects, and their names, that can create the conflicts and should be maintained when a field name changes.