Help! Lots of Various Problems!

[Sherrie]

Hi all!

I'm facing lots of problems with my database, which is attached.
thought I had some things settled, but apparently I did not. I got thi
far because of all the help I've received from this forum, and I reall
wish someone could help me with these:
I am having trouble calculating the Age on the form. I have used th
DateDiff function, but it works when the Date of Birth contains a date
When it doesn't I get an error. Also, I cannot clear the Age text bo
when I clear my form, as I received an error saying that I canno
assign a value to that field (I set it to "")

Also, I cannot seem to add date fields which are empty. I get an erro
saying that it is not a valid format. How do I correct this?

I am unable to edit my records. I get an error saying that I am missin
a With...End With statement. I cannot understand this as I have alread
put it in. There seems to be some problem with my SQL statement a
well. To test, please use the work permit number 0-12345678

I have an auto-numbered field (Employee ID) which is linked to th
Nationality. I update my counter everytime a new record has been added
but I get a message stating that someone has made changes before me an
to re-edit the record. I have managed to settle this by closing an
opening the form again... However, is there a better way to do this?
I really appreciate all the help I can get, being really new t
programming in Access and VB. I look forward to your advice an
suggestions.

Thanks again!

~Sharon

+----------------------------------------------------------------
| Attachment filename: eedetails.zip
|Download attachment: http://www.MSAccessForum.com.com/forums/attachment.php?postid=215557
+----------------------------------------------------------------

 

[John Vinson]

I'm facing lots of problems with my database, which is attached. I
thought I had some things settled, but apparently I did not. I got this
far because of all the help I've received from this forum, and I really
wish someone could help me with these:

Well, I don't download attachments but I expect I'll be able to help.

I am having trouble calculating the Age on the form. I have used the
DateDiff function, but it works when the Date of Birth contains a date.
When it doesn't I get an error.

Well... yes. When [Date of Birth] contains something that isn't a
date, that IS an error. You might be able to use an IIF() function;
set the Control Source of the Age textbox to

IIF(IsDate(Me![Date Of Birth]), DateDiff("yyyy", Me![Date Of Birth],
Date()) - IIF(Format(Me![Date Of Birth], "mmdd") >
Format(Date("mmdd"), 1, 0), NULL)

Also, I cannot clear the Age text box
when I clear my form, as I received an error saying that I cannot
assign a value to that field (I set it to "")

It's a calculated control; naturally you can't set it to anything
because it's not stored anywhere.

Also, I cannot seem to add date fields which are empty. I get an error
saying that it is not a valid format. How do I correct this?

How are you trying to "add an empty"? A Date *is not a text string* so
entering a string of blanks will not work (" / / " is not a
date!) Just leave the field empty, don't enter anything into it.

I am unable to edit my records. I get an error saying that I am missing
a With...End With statement. I cannot understand this as I have already
put it in. There seems to be some problem with my SQL statement as
well. To test, please use the work permit number 0-12345678

Sorry. Private database help is for paying clients only. Perhaps you
could post the VBA code or the SQL - SQL does not support WITH... END
WITH, and VBA code isn't a query.

I have an auto-numbered field (Employee ID) which is linked to the
Nationality. I update my counter everytime a new record has been added,
but I get a message stating that someone has made changes before me and
to re-edit the record. I have managed to settle this by closing and
opening the form again... However, is there a better way to do this?
I really appreciate all the help I can get, being really new to
programming in Access and VB. I look forward to your advice and
suggestions.

I don't understand this AT ALL. What is the meaning of "linked to the
Nationality" in this context?