Date type Mismatch

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that automatically pulls up the date with Date()
This for also has a field linked to a table that has the last date a certain
table was updated (no specific formatting).
When I run a macro to set the value of the liked field to Date() it gives me
a Type Mismatch error and will not update the field. This field has no
specific formatting.
Can anyone tell me what is mismatching? and how to correct it so it updates
the field linked to the table?
 
Please post the macro's actions or the VBA code that you're using and tell
us the table's structure, etc.
 
Table Name: <T: Date Updated Table>
Field Name: Date Updated
Data Type: Date/Time
Default settings

Macro Name: Macro1
Macro Command: SetValue
Item: [Forms]![F: Main Menu]![Date Updated]
Expression: Date()

Form Name: <F: Main Menu>
Form Field: Date Updated
Default Settings
 
James, I can't see what's wrong, but a couple of suggestions to help you
track it down.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

2. Open the form in design view.
Use the drop-down list at the left end of the Form Design toolbar to select
the control named Date Updated. Is it the text box bound to the date field?
Or is it some other object?

3. Open the form in normal view.
Choose a record where the Date Updated has a value.
Open the Immediate Window (Ctrl+G).
Enter:
? [Forms]![F: Main Menu]![Date Updated]
Does this yield a sensible result?

4. Still in the Immediate window, choose References from the Tools menu. Are
any of the checked libraraies marked with the word "MISSING"? More on
references:
http://allenbrowne.com/ser-38.html

Please post a reply if that has not tracked down the cause.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

James Kendall said:
Table Name: <T: Date Updated Table>
Field Name: Date Updated
Data Type: Date/Time
Default settings

Macro Name: Macro1
Macro Command: SetValue
Item: [Forms]![F: Main Menu]![Date Updated]
Expression: Date()

Form Name: <F: Main Menu>
Form Field: Date Updated
Default Settings
--
Thank you for your time.
Windows XP
Office 2002


Ken Snell (MVP) said:
Please post the macro's actions or the VBA code that you're using and
tell
us the table's structure, etc.
Click to expand...
Click to expand...
 
I have either verified or completed the steps below and none of those is the
issue.
--
Thank you for your time.
Windows XP
Office 2002


Allen Browne said:
James, I can't see what's wrong, but a couple of suggestions to help you
track it down.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

2. Open the form in design view.
Use the drop-down list at the left end of the Form Design toolbar to select
the control named Date Updated. Is it the text box bound to the date field?
Or is it some other object?

3. Open the form in normal view.
Choose a record where the Date Updated has a value.
Open the Immediate Window (Ctrl+G).
Enter:
? [Forms]![F: Main Menu]![Date Updated]
Does this yield a sensible result?

4. Still in the Immediate window, choose References from the Tools menu. Are
any of the checked libraraies marked with the word "MISSING"? More on
references:
http://allenbrowne.com/ser-38.html

Please post a reply if that has not tracked down the cause.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

James Kendall said:
Table Name: <T: Date Updated Table>
Field Name: Date Updated
Data Type: Date/Time
Default settings

Macro Name: Macro1
Macro Command: SetValue
Item: [Forms]![F: Main Menu]![Date Updated]
Expression: Date()

Form Name: <F: Main Menu>
Form Field: Date Updated
Default Settings
--
Thank you for your time.
Windows XP
Office 2002


Ken Snell (MVP) said:
Please post the macro's actions or the VBA code that you're using and
tell
us the table's structure, etc.
Click to expand...
Click to expand...
Click to expand...
 
With the form open, press Ctrl+G to open the Immediate Window, and enter:
? TypeName([Forms]![F: Main Menu]![Date Updated].Value)
What result do you get?

Then enter:
? Date()
Again what result do you get.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

James Kendall said:
I have either verified or completed the steps below and none of those is
the
issue.
--
Thank you for your time.
Windows XP
Office 2002


Allen Browne said:
James, I can't see what's wrong, but a couple of suggestions to help you
track it down.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

2. Open the form in design view.
Use the drop-down list at the left end of the Form Design toolbar to
select
the control named Date Updated. Is it the text box bound to the date
field?
Or is it some other object?

3. Open the form in normal view.
Choose a record where the Date Updated has a value.
Open the Immediate Window (Ctrl+G).
Enter:
? [Forms]![F: Main Menu]![Date Updated]
Does this yield a sensible result?

4. Still in the Immediate window, choose References from the Tools menu.
Are
any of the checked libraraies marked with the word "MISSING"? More on
references:
http://allenbrowne.com/ser-38.html

Please post a reply if that has not tracked down the cause.

James Kendall said:
Table Name: <T: Date Updated Table>
Field Name: Date Updated
Data Type: Date/Time
Default settings

Macro Name: Macro1
Macro Command: SetValue
Item: [Forms]![F: Main Menu]![Date Updated]
Expression: Date()

Form Name: <F: Main Menu>
Form Field: Date Updated
Default Settings
--
Thank you for your time.
Windows XP
Office 2002


:

Please post the macro's actions or the VBA code that you're using and
tell
us the table's structure, etc.
Click to expand...
Click to expand...
Click to expand...
 
? TypeName([Forms]![F: Main Menu]![Date Updated].Value)
Date
? Date()
1/16/2006
? TypeName([Forms]![F: Main Menu]![Todays Date].Value)
Date
? [Forms]![F: Main Menu]![Date Updated]
12/12/1999
? [Forms]![F: Main Menu]![Todays Date]
1/16/2006

I even removed the fields and remade them and still get the same error. I
also changed the [Forms]![F: Main Menu]![Date Updated] to a Date() and tried
to run the macro and still got a type mismatch error. I changed it back and
tried formating the field to different date styles and it does not work.
 
Sorry, James, I can't make any sense of the macro's Type Mismatch error.

If Access recognises the value in the text box as a Date data type, and it
also recognises and responds correctly to the Date() function, then I can
see no reason why it is unable to assign a date value to a control that
already contains a date.

The type mismatch typically means that an attempt has been made to assign
the wrong kind of data to a control (such as text to a number or date
field), or the wrong kind of data to a variable (such as a Null to an
Integer.) Your debug results indicate that the control already contains a
Date value, so attempting to asssign another Date should not result in a
type mismatch error.

I assume you have already tried clearing anything out of the Input Mask and
Format properties of the control, and that the Control Source is not an
expression or calculated control.

No idea what else to suggest.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

James Kendall said:
? TypeName([Forms]![F: Main Menu]![Date Updated].Value)
Date
? Date()
1/16/2006
? TypeName([Forms]![F: Main Menu]![Todays Date].Value)
Date
? [Forms]![F: Main Menu]![Date Updated]
12/12/1999
? [Forms]![F: Main Menu]![Todays Date]
1/16/2006

I even removed the fields and remade them and still get the same error. I
also changed the [Forms]![F: Main Menu]![Date Updated] to a Date() and
tried
to run the macro and still got a type mismatch error. I changed it back
and
tried formating the field to different date styles and it does not work.

--
Thank you for your time.
Windows XP
Office 2002


Allen Browne said:
With the form open, press Ctrl+G to open the Immediate Window, and enter:
? TypeName([Forms]![F: Main Menu]![Date Updated].Value)
What result do you get?

Then enter:
? Date()
Again what result do you get.
Click to expand...
Click to expand...
 
? TypeName([Forms]![F: Main Menu]![Date Updated].Value)
Date
Click to expand...

Eh? That sounds like it contains the four-letter text string Date
(which would indeed be a data type mismatch).

Perhaps you have its Default Value property set to "Date" rather than
to Date()?

John W. Vinson[MVP]
 
No, No! The value type is Date.
? TypeName([Forms]![F: Main Menu]![Date Updated].Value)
Date
? TypeName([Forms]![F: Main Menu]![Todays Date].Value)
Date
? [Forms]![F: Main Menu]![Date Updated]
1/17/2006
? [Forms]![F: Main Menu]![Todays Date]
1/17/2006
Anyway, I think it is a glitch in the program because when I created the
form from scratch and it worked. I compared both forms, all their settings
and they matched identically. After comparing everything the other form
started working. I have noticed other glitches too. Macros don't always
work unless you delete the line and reconstruct it from scratch too. So goes
my complaint today.

It works now for whatever reason. Thanks for the help anyways.
--
Thank you for your time.
Windows XP
Office 2002


John Vinson said:
? TypeName([Forms]![F: Main Menu]![Date Updated].Value)
Date
Click to expand...

Eh? That sounds like it contains the four-letter text string Date
(which would indeed be a data type mismatch).

Perhaps you have its Default Value property set to "Date" rather than
to Date()?

John W. Vinson[MVP]
Click to expand...
 
No, No! The value type is Date.

Anyway, I think it is a glitch in the program because when I created the
form from scratch and it worked. I compared both forms, all their settings
and they matched identically. After comparing everything the other form
started working. I have noticed other glitches too. Macros don't always
work unless you delete the line and reconstruct it from scratch too. So goes
my complaint today.

It works now for whatever reason. Thanks for the help anyways.
Click to expand...

I've seen this happen. Frustrating as can be!

One way I've *heard* that it can be cured is to use
Application.SaveAsText to save a Form or Report to an external text
file; delete the object; compact; and use Application.LoadFromText to
bring it back in.

John W. Vinson[MVP]
 
Back
Top