formating query fields

G

Giz

Hi,

Is there a way to set the format for a field in a query. I have built an
expression in a query field that combines the day, month, and year from 3
respective fields in a table so that my new "date" field in the query
combines all three. For reference it looks like this:

DATE: [year] & IIf([month]<10,"0" & [month],[month]) & IIf([day]<10,"0" &
[day],[day])

The result of this expression is a date that looks like "19940124", for
example, for jan 24, 1994. I want the result of this to be a number formatted
field, but the result is a text formatted field. Is there a way to designate
this as a number in a query, without having to make the query a "make table"
query then changing the format in the resulting table?? thanx
 
O

Ofer

Try a different and shorter approach for the format, and then convert it to
double


DATE: cdbl([year] & format([month],"00") & Format([day],"00"))
 
O

Ofer

I would advice you to change the name of the fields because they are resurved
words in Access, it might cause a problem.

Can you post the SQL you are using


Giz said:
This gives me the following result displayed in field for all records:
0u12ber. Although when I click on the cell it displays 12:00:00 AM

Ofer said:
Try a different and shorter approach for the format, and then convert it to
double


DATE: cdbl([year] & format([month],"00") & Format([day],"00"))



Giz said:
Hi,

Is there a way to set the format for a field in a query. I have built an
expression in a query field that combines the day, month, and year from 3
respective fields in a table so that my new "date" field in the query
combines all three. For reference it looks like this:

DATE: [year] & IIf([month]<10,"0" & [month],[month]) & IIf([day]<10,"0" &
[day],[day])

The result of this expression is a date that looks like "19940124", for
example, for jan 24, 1994. I want the result of this to be a number formatted
field, but the result is a text formatted field. Is there a way to designate
this as a number in a query, without having to make the query a "make table"
query then changing the format in the resulting table?? thanx
Click to expand...
Click to expand...
Click to expand...
 
G

Giz

sure, here it is.

SELECT [4_stations].COOPID, [year] & IIf([month]<10,"0" & [month],[month]) &
IIf([day]<10,"0" & [day],[day]) AS [DATE], [4_stations].Tmax,
[4_stations].Tmin, [4_stations].Prcp INTO Lincoln_climate
FROM 4_stations
WHERE ((([4_stations].COOPID)=245040) AND (([4_stations].year)>=1990));

which field's names should be changed? year, month, and day are the fields I
am referencing in the queried table. thanx again

Ofer said:
I would advice you to change the name of the fields because they are resurved
words in Access, it might cause a problem.

Can you post the SQL you are using


Giz said:
This gives me the following result displayed in field for all records:
0u12ber. Although when I click on the cell it displays 12:00:00 AM

Ofer said:
Try a different and shorter approach for the format, and then convert it to
double


DATE: cdbl([year] & format([month],"00") & Format([day],"00"))



:

Hi,

Is there a way to set the format for a field in a query. I have built an
expression in a query field that combines the day, month, and year from 3
respective fields in a table so that my new "date" field in the query
combines all three. For reference it looks like this:

DATE: [year] & IIf([month]<10,"0" & [month],[month]) & IIf([day]<10,"0" &
[day],[day])

The result of this expression is a date that looks like "19940124", for
example, for jan 24, 1994. I want the result of this to be a number formatted
field, but the result is a text formatted field. Is there a way to designate
this as a number in a query, without having to make the query a "make table"
query then changing the format in the resulting table?? thanx
Click to expand...
Click to expand...
Click to expand...
 
G

Giz

Not sure how to use the format function, I tried by keep getting errors. As
for using the View/Properties option, when I click on the grid column and try
to format the field, I have to type in number, and the result is n\um"ber".
?????

[MVP] S.Clark said:
Either use the Format function, or set the format property for the field.
View / Properties, click on the grid column.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting

Giz said:
Hi,

Is there a way to set the format for a field in a query. I have built an
expression in a query field that combines the day, month, and year from 3
respective fields in a table so that my new "date" field in the query
combines all three. For reference it looks like this:

DATE: [year] & IIf([month]<10,"0" & [month],[month]) & IIf([day]<10,"0" &
[day],[day])

The result of this expression is a date that looks like "19940124", for
example, for jan 24, 1994. I want the result of this to be a number
formatted
field, but the result is a text formatted field. Is there a way to
designate
this as a number in a query, without having to make the query a "make
table"
query then changing the format in the resulting table?? thanx
Click to expand...
Click to expand...
 
G

Giz

So I tried to change the field names in the table as below in the SQL, but
still get the same result in the records; each one has a value of (without
the quotes) "0u12ber". Here is the new SQL:

SELECT [4_stations].COOPID, CDbl([yearr] & IIf([monthh]<10,"0" &
[monthh],[monthh]) & IIf([dayy]<10,"0" & [dayy],[dayy])) AS Date1,
[4_stations].Tmax, [4_stations].Tmin, [4_stations].Prcp INTO Lincoln_climate
FROM 4_stations
WHERE ((([4_stations].COOPID)=245040) AND (([4_stations].yearr)>=1990));


Ofer said:
Day, month, Year and Date are resurved words in access, each one of them will
return its on value from a date field
Year(DateField) will return the year
Month(DateField) will return the Month
Day(DateField) will return the Day
date will return the current date

You must change the name of the fields


If your query works now and it returns the value of the date in string
format but you want to change it to number then try this.

SELECT [4_stations].COOPID, cdbl([year] & IIf([month]<10,"0" &
[month],[month]) & IIf([day]<10,"0" & [day],[day])) AS [DATE],
[4_stations].Tmax,
[4_stations].Tmin, [4_stations].Prcp INTO Lincoln_climate
FROM 4_stations
WHERE ((([4_stations].COOPID)=245040) AND (([4_stations].year)>=1990));



Giz said:
sure, here it is.

SELECT [4_stations].COOPID, [year] & IIf([month]<10,"0" & [month],[month]) &
IIf([day]<10,"0" & [day],[day]) AS [DATE], [4_stations].Tmax,
[4_stations].Tmin, [4_stations].Prcp INTO Lincoln_climate
FROM 4_stations
WHERE ((([4_stations].COOPID)=245040) AND (([4_stations].year)>=1990));

which field's names should be changed? year, month, and day are the fields I
am referencing in the queried table. thanx again

Ofer said:
I would advice you to change the name of the fields because they are resurved
words in Access, it might cause a problem.

Can you post the SQL you are using


:

This gives me the following result displayed in field for all records:
0u12ber. Although when I click on the cell it displays 12:00:00 AM

:

Try a different and shorter approach for the format, and then convert it to
double


DATE: cdbl([year] & format([month],"00") & Format([day],"00"))



:

Hi,

Is there a way to set the format for a field in a query. I have built an
expression in a query field that combines the day, month, and year from 3
respective fields in a table so that my new "date" field in the query
combines all three. For reference it looks like this:

DATE: [year] & IIf([month]<10,"0" & [month],[month]) & IIf([day]<10,"0" &
[day],[day])

The result of this expression is a date that looks like "19940124", for
example, for jan 24, 1994. I want the result of this to be a number formatted
field, but the result is a text formatted field. Is there a way to designate
this as a number in a query, without having to make the query a "make table"
query then changing the format in the resulting table?? thanx
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer

Day, month, Year and Date are resurved words in access, each one of them will
return its on value from a date field
Year(DateField) will return the year
Month(DateField) will return the Month
Day(DateField) will return the Day
date will return the current date

You must change the name of the fields


If your query works now and it returns the value of the date in string
format but you want to change it to number then try this.

SELECT [4_stations].COOPID, cdbl([year] & IIf([month]<10,"0" &
[month],[month]) & IIf([day]<10,"0" & [day],[day])) AS [DATE],
[4_stations].Tmax,
[4_stations].Tmin, [4_stations].Prcp INTO Lincoln_climate
FROM 4_stations
WHERE ((([4_stations].COOPID)=245040) AND (([4_stations].year)>=1990));



Giz said:
sure, here it is.

SELECT [4_stations].COOPID, [year] & IIf([month]<10,"0" & [month],[month]) &
IIf([day]<10,"0" & [day],[day]) AS [DATE], [4_stations].Tmax,
[4_stations].Tmin, [4_stations].Prcp INTO Lincoln_climate
FROM 4_stations
WHERE ((([4_stations].COOPID)=245040) AND (([4_stations].year)>=1990));

which field's names should be changed? year, month, and day are the fields I
am referencing in the queried table. thanx again

Ofer said:
I would advice you to change the name of the fields because they are resurved
words in Access, it might cause a problem.

Can you post the SQL you are using


Giz said:
This gives me the following result displayed in field for all records:
0u12ber. Although when I click on the cell it displays 12:00:00 AM

:

Try a different and shorter approach for the format, and then convert it to
double


DATE: cdbl([year] & format([month],"00") & Format([day],"00"))



:

Hi,

Is there a way to set the format for a field in a query. I have built an
expression in a query field that combines the day, month, and year from 3
respective fields in a table so that my new "date" field in the query
combines all three. For reference it looks like this:

DATE: [year] & IIf([month]<10,"0" & [month],[month]) & IIf([day]<10,"0" &
[day],[day])

The result of this expression is a date that looks like "19940124", for
example, for jan 24, 1994. I want the result of this to be a number formatted
field, but the result is a text formatted field. Is there a way to designate
this as a number in a query, without having to make the query a "make table"
query then changing the format in the resulting table?? thanx
Click to expand...
Click to expand...
Click to expand...
 
G

Giz

This gives me the following result displayed in field for all records:
0u12ber. Although when I click on the cell it displays 12:00:00 AM

Ofer said:
Try a different and shorter approach for the format, and then convert it to
double


DATE: cdbl([year] & format([month],"00") & Format([day],"00"))



Giz said:
Hi,

Is there a way to set the format for a field in a query. I have built an
expression in a query field that combines the day, month, and year from 3
respective fields in a table so that my new "date" field in the query
combines all three. For reference it looks like this:

DATE: [year] & IIf([month]<10,"0" & [month],[month]) & IIf([day]<10,"0" &
[day],[day])

The result of this expression is a date that looks like "19940124", for
example, for jan 24, 1994. I want the result of this to be a number formatted
field, but the result is a text formatted field. Is there a way to designate
this as a number in a query, without having to make the query a "make table"
query then changing the format in the resulting table?? thanx
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

Try the following expression, which takes your fields and builds a date with
date serial, applies a format on the date (which converts it to a string, and
then converts the string to double number form.

CDbl(Format(DateSerial([Year],[Month],[Day]),"yyyymmdd"))

By the way, your field names are all reserved words and have specific meanings
in Access.

Date = The current system date
Year = returns the year of some date
Month = returns the month of some date
Day = returns the day of some date.

As long as you keep them surrounded by brackets or otherwise delimited, you
probably won't have a problem. I would suggest that if at all possible you
rename them.

Date to ActionDate
Year to ActYear
etc.
 
M

[MVP] S.Clark

The help file describes the use of all functions.

e.g.
DayAndDate= Format(Date(), "dddd, mmm d yyyy")

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting

Giz said:
Not sure how to use the format function, I tried by keep getting errors.
As
for using the View/Properties option, when I click on the grid column and
try
to format the field, I have to type in number, and the result is
n\um"ber".
?????

[MVP] S.Clark said:
Either use the Format function, or set the format property for the field.
View / Properties, click on the grid column.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting

Giz said:
Hi,

Is there a way to set the format for a field in a query. I have built
an
expression in a query field that combines the day, month, and year from
3
respective fields in a table so that my new "date" field in the query
combines all three. For reference it looks like this:

DATE: [year] & IIf([month]<10,"0" & [month],[month]) & IIf([day]<10,"0"
&
[day],[day])

The result of this expression is a date that looks like "19940124", for
example, for jan 24, 1994. I want the result of this to be a number
formatted
field, but the result is a text formatted field. Is there a way to
designate
this as a number in a query, without having to make the query a "make
table"
query then changing the format in the resulting table?? thanx
Click to expand...
Click to expand...
Click to expand...
 
Top