number fields in text to number field, formula for fiscal year (1 Viewer)

S

shurleexyz

Guest
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:34
Joined
Feb 19, 2002
Messages
42,984
Just copying the text field to a numeric field won't convert the text field to a date recognizable by Access. You will need to format the text date into a string that Access recognizes as a date and then store the date in a field defined as date/time rather than numeric. Once the field is recognized as a date, you will be able to use all the date functions on it.

Select CDate(Left(DOB, 2) & "/" & Mid(DOB, 3, 2) & "/" & Right(DOB, 2)) as NewDOB, ....

Then you can use DateAdd() to subtract 2 months from a date to determine its fiscal year.
 
S

shurleexyz

Guest
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:34
Joined
Feb 19, 2002
Messages
42,984
Life is sooooo much easier when month and year information is stored as an actual date/time data type. That allows you to use all the VBA date functions rather than having to work them out yourself. If you subtract 3 (I know I said 2, gut that was a typo) months from a real date in April you will get a real date in January. If you use the the DatePart() or Format() function to obtain the quarter - which will be 1. Or you can write your own function:

Code:
Public Function FiscalQuarter(InDate as Date) As Integer
    Select Case Month(InDate)
        Case 1, 2, 3
            FiscalQuarter = 4
        Case 4, 5, 6
            FiscalQuarter = 1
        Case 7, 8, 9
            FiscalQuarter = 2
        Case 10, 11, 12
            FiscalQuarter = 3
        Case Else
            FiscalQuarter = 0
    End Select
End Function
 
S

shurleexyz

Guest
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:34
Joined
Feb 19, 2002
Messages
42,984
Year and month are poor choices for field names since they are also function names and if you inadvertantly forget to surround them with [], Access will interpret what YOU think is a reference to YOUR field as a reference to a function. This won't raise an error but it will cause invalid results! Also, rather than store two numeric fields, why not store a complete. Just use the first of the month since you don't care what the day is. If you want to hide the day and just show the month and year, you may have to use an unbound field so you can force the correct assumption. When you pass 2 out of 3 parts of a date, VBA attempts to figure out which piece is missing and choose a logical default. I don't know what country you live in so I don't know what your standard date format is but be sure to test carefully.

Your also have a lot of other poorly formed names. table/column/object names should NEVER contain embedded spaces or special characters. VBA doesn't accept these names and will change them behind the scenes which leads to strange problems. Other languages are not so nice, they will simply refuse to accept names with spaces or punctuation characters.
 
S

shurleexyz

Guest
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
 

Users who are viewing this thread

Top Bottom