merging fields

[Jen0dorf]

HI

|Spent a few hours this week trying to to this but have failed



I have a table containing fields

day
month
year

which will show as

3
jan
2016

I want to merge the fields and then show them in a UK date format

ie 01/01/2016

The table supplied has 32000 records so a manual update is a bit extreme.

It seems I can do it in Excel but that seems a bit longwinded

Has anyone any ideas?

thanks

Ia

[LaughingBull]

the code is Day & " " & Month & " " & Year the " " gives you a space between each field

[June7]

or

Format([Day],"00") & "/" & Format([Month],"00") & "/" & [Year]

Day, Month, Year are reserved words - in this case they are all intrinsic functions. Should not use reserved words as names.

[Jen0dorf]

Hi

appreciate the quick response but I'm sorta even more confused.

I discovered from my web research that I could populate a form by using a query such as you suggest ( using the property sheet and the datasource option.( do I sound like I know what I'm saying? don't be fooled a little knowledge.....)

I wanted to combine the fields into a new field and then delete the day, month, year fields. I've discovered that the previous developer was unable to search by a month or year unless they were in different fields but my research appears to make this relatively easy!! Hence I want to combine the fields. IN reality I have 3 lots of 34000 records to process - date of birth, date of death and date of burial!

Any way how can I run your query so that it creates my new field. I did find some software to do it from hot hot software but I want to learn

thanks

Ian

[June7]

Dates are actually stored as a number value. Access translates this number to a display structure in a date/time field that people can easily interpret. The display default in Access is U.S. structure - MM/DD/YYYY. Using other structures is problematic. Review: http://allenbrowne.com/ser-36.html

This may be why the developer set up separate fields for the date parts.

[LaughingBull]

create a query and watch this video https://www.youtube.com/watch?v=2-uJZo63bYM

[Jen0dorf]

Hi

thank for all the responses.

Putting it simply is there any advantage to my merging
DayOfDeath
MonthOfDeath
YearOfDeath

into one field?

when I get to the front end part I will want to add a search option to filter by year and or month and or day

But I am following the advice give n and trying to get the tables and relationships sorted first.

I have the relationships and now it's the tables

thanks

Ian

[LaughingBull]

in your query in a blank field merge them together and when you run the query that field will show the month day and year of those records selected from the query but only if it is important for your report other wise no advantage that I can think of

[June7]

Did you read the Allen Browne article about international dates from the link in post 5?

[Jen0dorf]

Hi

I did read the article and sort of understood it which is why I began to think that the idea of having the three fields is not a bad idea. hence my question.

I do remember that some years ago I was working on a non ms database and they calculated dates counting from a fixed date - I thin k the start of the Gregorian calendar and then the day count was converted into the date display - but that was when the disk drives were 5 1/4 inch

Seems from the article that Access does not like non US formats and I assume that that has not changed in the past 8 years?

So I think I'll stick to separate day month and year fields.

Have read over the past week that I could concatinate those fields to display in a normaldate format so perhaps I'll give that consideration when I get to the form stage

thanks again on and all

Ian