concatination issue

[Jen0dorf]

Hi



have an issue with a parish records database I am creating using spreadsheets supplied by someone else.

The dates are designated by two fields:

dateOfBaptism - which I have designated a date/time field with the mask dd/mm the original spreadsheet entry was in the form of 22-AUG

YearOfBaptism - which I have designated a short text field - the entries are 1812 or 1878 etc etc, the original spread sheet entry is 1875 or 1898 etc.

OK I have been asked if it is possible to create a query to put the records in date order. including day, month and year. ie so 1/2/1800 comes before 7/2/1800 etc.

So I want to merge the two records and then define the new field as a date format so I can sort by date in a query.

I know I can create a calculated field to concatinate the fields but that does not seem to do what I want.

Any ideas anyone?

thanks

Ian

[orange]

YearOfBaptism - which I have designated a short text field - the entries are 1812 or 1878 etc etc, the original spread sheet entry is 1875 or 1898 etc.

What exactly does the or mean? You could use a number/integer datatype.
If all you have is a Year value, you'll have to be creative to get Month and Day. How do you "divine" a Month and Day from no info? You could arbitrarily make it 1 January, but that's no better than 13 September.

[Jen0dorf]

Hi

sorry I confused the issue - as usual

field one contains day and month in the form of 25-11

field two contains the year in the form of 1878

I'd like to merge/concatinate to 25-11-1878 so I can sort by date

IAn

[orange]

Ok, I would recommend something like this:

sDayMonth contains your 25-11
SYear contains 1878

dDateBaptism = CDate(format(sdayMonth & sYear,"dd-mm-yyyy"))

where dDateBaptism is a Date/time datatype and should sort properly as well as behave with any Date related functions.

sample test:

Sub IanTest()
    Dim sDayMonth As String   ' string variable to hold dd-mm
    Dim sYear As String           'string variable to hold year YYYY
    Dim dDateBaptism As Date 'date variable to hold the date after some processing on concatenated strings
    sDayMonth = "25-11"
    sYear = "1878"
    dDateBaptism = CDate(Format(sDayMonth & "-" & sYear, "dd-mm-yyyy")) 'you need the "-" to complete the format
    Debug.Print dDateBaptism

End Sub

Result:

25/11/1878 ----I'm in Canada and DD/MM/YYYY is my regional setting date display

[Jen0dorf]

Hi

thanks for that, just going away for a few days but I'll try it on my return

thanks

Ian

[Jen0dorf]

Hi

just had a look before I go away - delayed at airport, what fun

Think I can see where you are coming from and it is a few levals above my ability!


I can see you are setting up two variables, using them to create a third and then populating a new field with a formated value .

But I don't get the mechanics of how to use the code. ie how to apply it to my table

thanks

Ian

[orange]

Sorry Ian for any confusion.

My code was a mock-up of your situation -- 2 texts to be combined to make a legitimate Date.

Main point, you can concatenate 2 text strings and make a Date.

These 2 lines just define 2 strings with values (from your sample).

sDayMonth = "25-11"
    sYear = "1878"

This is where the "concatenation and conversion occur

CDate(Format(sDayMonth & "-" & sYear, "dd-mm-yyyy"))

The & concatenates the strings
Format tells Access how to interpret the concatenation
CDate is a built in function that converts a "character string" to a CDate


Have a good trip.