help with date/time..i don't want 00:00:00 i don't use the time

[lilakathy]

i received this excellent advice: Use the Format function in your query to export in the format you wish to:
Format(YourDateField,"dd/mm/yy") but i cannot figure out HOW or WHERE i enter this information. when exporting date/time field i get 00:00:00 along with the date. i do not want it but i have read and read and cannot figure out how to get rid of it. i know a lot of questions are asked about the date but it it not explained that great in HELP or the index..

 

[RV]

In the SQL view of your query, type the Format function round your field / column, e.g.

SELECT Format(YourDateField,"dd/mm/yy")
FROM YourTable;

If you search in the Access help , the format function is quite well explained.
You can also search this forum, there are lots of posts about formatting dates.

P.S. Repost in the original post

Suc6,

RV

 

[lilakathy]

hi. I reread the date formatting again in help. i tried different combinations of codes in the sql view and in the "properties box" of my two date columns. i am still getting it wrong.
This is exactly how my sql view looks in my query:
SELECT ABD.[DocNo:], ABD.[1st Rev Recv'd], ABD.[Rush3:], ABD.[Drafter2:], ABD.[Returned to Atty2:], ABD.[Comments3:], ABD.[New2:], ABD.[Rev2:], ABD.[Revision/Bip]
FROM ABD
WHERE (((ABD.[DocNo:])="97-2521"

the two fields 1st Rev Recv'd and Returned to Atty2 are the date fields i need help with the formatting. i would happily pay someone for their valuable time in helping me with this. i have honestly spent hours and days and am just not getting it...

 

[RV]

No need to pay.
Try this:

SELECT ABD.[DocNo:], ABD.Format([1st Rev Recv'd],"dd/mm/yy"), ABD.[Rush3:], ABD.[Drafter2:], ABD.Format([Returned to Atty2:],"dd/mm/yy"), ABD.[Comments3:], ABD.[New2:], ABD.[Rev2:], ABD.[Revision/Bip]
FROM ABD
WHERE ABD.[DocNo:]="97-2521"

RV

 

[lilakathy]

oh boy....we are getting sooo close (notice is said "we"
i copied and pasted your exact statement.
an error box pops up and says this: undefined function 'abd.format' in expression


when I click on it further, a window opens up and it reads:
Undefined function <name> in expression. (Error 3085).
You entered an SQL expression that includes a Function procedure name that can't be recognized. Make sure the function exists, that it can be used in SQL expressions, or check the expression to make sure you entered the name correctly.

RV, thank you for helping me back and forth.. especially on sunday and easter..

 

[RV]

Sorry,

my mistake.
The statement should be:

SELECT ABD.[DocNo:], Format(ABD.[1st Rev Recv'd],"dd/mm/yy"), ABD.[Rush3:], ABD.[Drafter2:], Format(ABD.[Returned to Atty2:],"dd/mm/yy"), ABD.[Comments3:], ABD.[New2:], ABD.[Rev2:], ABD.[Revision/Bip]
FROM ABD
WHERE ABD.[DocNo:]="97-2521"

RV

 

[lilakathy]

i copied and pasted exactly. when i exported the query, the export text wizard window said: too few parameters. expected 1.

so i tried to open the query and a window popped up that wants a parameter. i made up a date for the fun of it and that is the date that printed in the datasheet view. i looked at the sql view and it changed all by itself to:


SELECT ABD.[DocNo:], Format(ABD.[1st Rev Recv'd],"dd/mm/yy") AS Expr1, ABD.[Rush3:], ABD.[Drafter2:], Format(ABD.[Returned to Atty2:],"dd/mm/yy") AS Expr2, ABD.[Comments3:], ABD.[New2:], ABD.[Rev2:], ABD.[Revision/Bip]
FROM ABD
WHERE (((ABD.[DocNo:])="97-2521"));

i tried it three times but the same thing happens..

i do not blame you if you do not want to continue on. it must be very hard to solve a problem like this over the internet. i sincerely do appreciate your time.

 

[lilakathy]

RV, i am feeling really stupid. i was studying your statement and now i notice that you put in the "Expression 1" and "Expression 2". i stated in my last reply that access changed it all by itself.

 

[Pat Hartman]

Please don't post the same question under multiple topics. You have at least two threads going here on this topic and so you are fragmenting the help you are receiving and probably confusing yourself.

[This message has been edited by Pat Hartman (edited 03-31-2002).]

 

[lilakathy]

sorry..if i keep replying to the same response..i thought that was a thread. i did post the similar question under "tables" a few days ago but i saw the error in that and quit. but i am not confused, actually i just read the statement wrong, but i did not copy it wrong. i still have the same problem and i am still stuck.

 

[Pat Hartman]

At the moment we are just guessing. Why don't you post the SQL for the query.

 

[RV]

Expr1 and Expr2 are aliases and yes, Access comes up with aliases by itself when using functions in a query.
You can use an alias to present a column name with a more functional and understandable name.

You can replace Expr1 and Expr2 by whatever label / description you like....

>i copied and pasted exactly. when i exported the query, the export text wizard window said: too few parameters. expected 1.<

The ONLY reason for this error is that your query contains a fieldname which isn't recognized as
a columnname in the table or query your query is based on.
You'll have to compare for yourself each columnname in your query with the columnname in the table or query your query is based on..

Suc6,

RV


[This message has been edited by RV (edited 04-01-2002).]

 

[lilakathy]

this is the SQL View of the query i am trying to export before i tried the new recommended statement. (the two date fields are ABD Recv'd and Returned to Atty2.)

SELECT ABD.[DocNo:], ABD.[ABD Recv'd:], ABD.[Rush3:], ABD.[Drafter2:], ABD.[Returned to Atty2:], ABD.[Comments3:], ABD.[New2:], ABD.[Rev2:], ABD.[Revision/Bip]
FROM ABD
WHERE (((ABD.[DocNo:])="97-2521"));


this is the recommended query I tried:

SELECT ABD.[DocNo:], Format(ABD.[1st Rev Recv'd],"dd/mm/yy") AS Expr1, ABD.[Rush3:], ABD.[Drafter2:], Format(ABD.[Returned to Atty2:],"dd/mm/yy") AS Expr2, ABD.[Comments3:], ABD.[New2:], ABD.[Rev2:], ABD.[Revision/Bip]
FROM ABD
WHERE (((ABD.[DocNo:])="97-2521"));

when i try to export the new suggested query the Export Text Wizard window pops up and says too few parameters. expected 1.
when i just try to open the query, an Enter Parameter Value window pops up and asks for a parameter for 1st Rev Recv'd. this is just a date field for the date we receive information. i don't know what kind of parameter it wants. i have 3,000 records to export but i just chose one (97-2521) to see if i could export it. is this enough information for you to see what trouble i am having?

 

[Pat Hartman]

The parameter box is popping up because the name in the query differs from the name in the table. Using spaces and special characters in field names is a very BAD idea. If you ever get to the point where you need to "upsize" your database to SQL server or some other backend RDBMS, you will need to rename every one of your fields since these names will not be acceptable.

In any event, the culpret is most likely a missing or extra space. Open the db in design view and copy the problem field name and paste it into the query. That should fix the parameter prompt problem.