number fields in text to number field, formula for fiscal year

[shurleexyz]

Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?

There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.

Thank you

 

[shurleexyz]

change number fields in text to numbers

Thanks Pat for your reply
More information on the file. The original file in access is being collected for a survey so the DOB, length of stay fields have to remain as text. There have been more fields added to this survey file so we can collect data for our own use. Query had been set up to change Date of Birth field from text to read as a date so information can be extracted for our use.

Each month data is input into the main survey file and only data required for the survey is exported with a query called (add to export) to the company doing the survey and then that file is deleted so I have to be careful about changing their Date of Birth, length of stay fields, Record #, Visit #, and Visit Date that are set up as text.

This is my first project for working in Access. Should I set up queries to change their length of stay, Record #, Visit# and Visit Date to date/time or number files? What would be the strings for those? .... can this all be done in the main survey file? What is the best way to handle this?

Your reply regarding determine fiscal year - I am not clear on that. We have a column where the year is input monthly and a column where the month is input monthly. You suggested DateAdd() to subtract 2 months. Please explain further so I can Have April, May, June counted as fiscal quarter 1, July, Aug Sept as fiscal quarter 2, Oct, Nov, Dec as fiscal quarter 3, Jan, Feb, Mar as fiscal quarter or 4 or have the months number starting as April - 1 and May- 2 etc.

Thanks for your help.

 

[shurleexyz]

new database set up from file that fields were all text

Thanks for your help Pat.
I have set up a new database that has the proper number fields and date/time for other fields instead of being in text.

I have now had to add more information to the table. I am attaching a copy of the table now.

These are the other fields that I would have to add, some would be duplication of the fields that I already have so I will have to delete them out of original database and probably set up
tables for

PatientID
Admitted from
Diagnosis
Date of Referral
Assessemnt Date
Assessment Wait Time
Approval Date
Admission Date
Total Waiting Time

another table for
Patient ID
Discharged to
Diagnosis
Date IN
Date Out
Length of Stay

another table for
Beginning Berg Score
Ending Berg Score
Score Difference
Discharged To

I realize that there are some field names in the attachment that are duplicated in these new tables that I have to add and would have to be taken out.

How would you set up the information above and in the attachment up in a database?

Thanks so much for your help.

shurleexyz

I was not able to attach file so here goes - these were in my original file and I have now changed some fields to number and date/time.

PatientID autonumber
Year number
month number
salutation text
FName text
LastName text
Address text
Address2 text
City text
PostalCode text
Province text
Gender text
Date of Birth Date/Time
medical number number
visit number number
visit date date time
length of stay number

 

[shurleexyz]

new database set up from file that fields had been all text

Hello Pat
I am from Canada and I have set up dates which I think are the standard date format but I will test them. Thanks for the information about year and month being poor choices for field names. I will see if I can change this.

The original survey had been entered as monthly database files and then the monthly files were appended to one large database that had all the fields in it as text files (those text files can not be changed because of the survey and I will continue to send my survey results from the original survey database).

As this is my first project to change, I found it was going to be easier to set up the new database that has the proper field format such as (number and date/time fields in it).

I will change the names so that they do not have spaces.

The last paragraph that I typed of the names from the original file shows the file name and then the field format is listed behind the file name e.g. DateofBirth Date/Time - Date/Time is the field format for that field name

I am not clear on special characters or punctuation characters you mentioned. Is this what you meant by looking at the last paragraph as it may not be very clear.
The last paragraph that I typed of the names from the original file show the file name and then the field format is listed behind the file name e.g.
DateofBirth Date/Time - (Date/Time is the field format for that field name)

should have read

field name field format
PatientID autonumber
year number
month number
saluation text
FName text
LastName text
Address text
Address2 text
City text
PostalCode text
Province text
Gender text
DateofBirth Date/Time
MedicalNumber number
VisitNumber number
VisitDate Date/Time
LengthofStay number

Pat Thanks for your assistance

Shurleexyz