[mso] Re: Access: Converting Date To Year For Parameter Look Ups

Jim, I agree, I'm thinking the data is bad, too, since it wouldn't copy and
your code got the sample database working great with their data.  I've
re-done per below instructions and am getting the report to work ok, by
manually entering a year requested when the query runs straight from the
report, but not able yet to trigger it from the parameter form, getting
same 'value entered isn't valid for this field' error with corrected data.
Going to work with the form some more and make sure I didn't miss putting
any of your code anywhere.  Hoping it's just something simple I've missed,
because now all the other parts are operating correct and I feel like we're
almost home!

Thank you so much for all your kind and patient help, it has made all the
difference in this :-) !
Cathy


                                                                                
                                          
                      "Jim Pettit"                                              
                                          
                      <j_e_pettit@hotmail      To: <mso@xxxxxxxxxxxxx>          
                                          
                      .com>                                                     
                                          
                      Sent by:                 cc:                              
                                          
                      mso-bounce@freelist                                       
                                          
                      s.org                    Subject:  [mso] Re: Access: 
Converting Date To Year For Parameter Look Ups 
                      02/24/2004 03:50 PM                                       
                                          
                      Please respond to                                         
                                    ..... 
                      mso                                                       
                                          
                                                                                
                                          
                                                                                
                                          




Cathy--

1) The setting for four-year formatting can be found under 'Tools >
Options > General > Use four-digit year formatting'.

2) You stated, "...when I tried to open the report got a 'value entered
isn't valid for this field' error." and "...I decided to copy my data to
the tblPatientInformation table to try to fool it...It wouldn't copy."
Why wouldn't it copy? It sounds more and more to me as though you've got
a piece of bad data. Have you tried repairing the database? You could
also try renaming the original table, then duplicating the table
structure only, then populating that table with a bunch of temporary
data, then seeing if it works with the form. If that works, the error is
probably in your data. If not, well...

Let me/us know what happens.

--Jim

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Cathy.Evans@xxxxxxxxx
Sent: Tuesday, February 24, 2004 11:57 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Access: Converting Date To Year For Parameter Look
Ups



Jim, thank you.  I made the below changes to the sample database and it
worked.  Fantastic.  Next step was to transpose the success to my table,
etc.  Got the drop down arrow to return only the 4 yr dates entered, but
when I tried to open the report got a 'value entered isn't valid for
this field' error saying 'you may have entered text in a numeric field
or a number that is larger than the FieldSize setting permits.

So I decided to copy my data to the tblPatientInformation table to try
to fool it :-).  It wouldn't copy, and I noticed in the design view that
Rogers sample database had the following filter in the date/time field:
99/99/00;0;_.  When I put a date in that field, it makes the year a 4
digit year.  I remember recently finding a setting in my Access, I
thought in tools/option, that set all dates to view as 4 digit.  At the
time I told it yes, but now I'd like to toggle that setting off and see
it it's causing problems but I can't find it.

The below works perfectly within Rogers database, so if I can't find the
problem in mine, I'm thinking of using the one I revised per your
instructions and importing my data into his preformatted table and
importing all my forms/reports/queries and go from there.  There could
be some problem with my original table, otherwise why would it work in
one database and not another?  Anyway, I greatly appreciate all your
help, there is so much to learn!

Hope I haven't confused you further, I know I'm already there! Regards,
Cathy






-----------------------------------------------------------------------------------------------------
The information transmitted is intended only for the person
or entity to which it is addressed and may contain confidential
and/or privileged material. If you are not the intended recipient 
of this message you are hereby notified that any use, review,
retransmission, dissemination, distribution, reproduction or any
action taken in reliance upon this message is prohibited. If you 
received this in error, please contact the sender and delete the 
material from any computer.  Any views expressed in this message
are those of the individual sender and may not necessarily reflect 
the views of the company.  
-------------------------------------------------------------------------------------------------------

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: