Pain in the Access

[tourless]

Hi Folks.

Please be gentle as I am not of the Access world. I'm working a linked DB in Access 2003 and I'm looking ot create a query with calculated columns as part of the result set. I can add my table and fields in Query View, and select simple critera such as a machine number, but I need to do a few more things.

1. Use 'today' as a date range. -I've tried the seeminlgy obvious =Today(), but apparently that is an undefined function in Access.
2. I have a time field that is stored in seconds that I need to be able to convert to a readable hh:mm time. -My thought was to create a new field in my query with something like this... [RealTime]=[Process!Time]/86400, but when I do that it prompts me to enter a value, not calculate when I'm looking for.

I know this is probably some basic access stuff but I have zero background and no time to search through idk how many google searchs to try and find my answers. So I come to you, the people in the know, to hopefully point me in the right direction. Any assistance is always greatly appreciated.

 

[alansidman]

Use Now() which will give you date and time.

 

[james_lankford]

1:
WHERE
Table1.dt >= Date() and Table1.dt <= dateadd('d', 1, Date() )

 

[welshgasman]

Probably need to use Date or Date()
Take your Excel head off an be prepared to learn Access methods. They are not there for fun, Excel and Access are two different beasts.

 

[Micron]

If those answers don't help, explain what these portions are: [RealTime], [Process!Time]
1. Anything in Access queries within brackets is taken to be a field or table name. If it cannot be resolve you will get prompted.
2. Using any special character save for underscore in an object name is ill advised.

Process!Time looks like a workbook!sheet reference and that is not syntactically correct in Access but it could be an actual field name. If it is, see point number 2.
In query design grid, a calculate field name would be followed by a colon, as in RealTime: calculating expression goes here The calculate field name must play by the naming rules for fields.

 

[tourless]

Thanks for the input everyone. I'm not sure what time I'm going to have to look into this today but I appreciate all the responses and will report back when I have some more information.
I do need to get my head out of excel and start learning access methods and functions, I agree.

 

[alansidman]

Here is a link that you may find helpful in getting your head into Access mode.

fundamentals-of-relational-database-design.doc | Powered by Box

app.box.com