For the Date field:
Code:
Format([Date], "mm/dd/yyyy")
...
dizzydangler
Last Activity: Jan 12 '10, 03:16 AM
Joined: Nov 5 '09
Location:
-
SORTED! Discovered the Format function, which I used in an update query to truncate all the date and time fields and give me exactly what I want in each field. For those that might be looking for a similar solution in this thread, I put the following expressions in the "Update To:" row of the Update Query
For the Date field:
For the Time field:
... -
Thanks, NeoPa. I think I'm on the right track, now. I need to be able to do more than just search between two dates--I need to query for all results before a certain date, all results after a certain date, and allow variations for date-only searches vs. Date/Time searches. I've been experimenting with ways to do this using the Select Case function and Cases consisting of different "Null" or "Not Null" combinations of the four...Leave a comment:
-
OK, I think I've gotten over the date/time hurdle. I replaced any null values for time in the table of saved records with "0:00," and made the input form require a time input, thus eliminating the possiblity of a null time field in any record in the database. I've also been able to write expressions to query by date/time if both Date and Time controls are filled in on the query form.
I mentioned previously that my query form...Leave a comment:
-
Stewart, that's brilliant--I had no idea you could just add date + time values to form date/time fields. I think I'm on the right track now. Still haven't gotten the code to work, but I haven't played with it too much yet. What happens if a null value gets added in the [some time] field? Can the resulting field (which I would think would still be just a date field) still be compared with a date/time field? Or does it create a date/time field...Leave a comment:
-
Thanks, ChipR, but that expression returns a date from the text string--what I want to do is combine the date field and the time field into a date/time field that I can then use in an IIF statement to set the start date/time and end date/time in my query parameters. I tried to use both '[Date]' and '[Time]' as text in the CDate function, like this:
Also tried:Code:CDate("[Date]","[Time]")
...Leave a comment:
-
Create date/time field from separate date and time entries
Just a quick question...I'm running an MS Access 2007 db that tracks appointments in a single table. Date and time are entered as separate fields in short date (mm/dd/yyyy) and short time (hh:mm) format. I've been running queries by date alone, which has been working, but there are times when I have to run queries based on times as well. (e.g. return all apointments that occurred after 1:00 PM on 12/15/2009 and before 1:00 PM on 12/22/2009). ... -
Great explanation, nsbecker--helps me not only to understand why I was getting the error, also helps me visualize what the procedures themselves mean/are doing, so I can select the ones I need in the future. Thanks, all!Leave a comment:
-
OK, working through the form properties, I built the command under the Form_Open() sub--still no luck. Access help seems pretty worthless when it comes to figuring out definitions for commands/functions/etc, so I just went down the list trying each one. Form_Load() seemed to give me the same results as Form_Activate() . Would this be a better event procedure to use?Leave a comment:
-
OK, Form_Open() gave me a "procedure declaration does not match description of event" error, but I got it to do exactly what I want (open w/blank controls ready to enter a new record, but allow user to scroll backwards/forwards to view and edit previous records) with the Form_Activate() event procedure. Thanks!
JoeLeave a comment:
-
Mary, NeoPa's correct--as I think I posted in another thread, the users are all former excel junkies who are more comfortable manipulating a spreadsheet. This is why I've been trying to migrate the data and maintain it in access. We had about 12 different copies of a the old spreadsheet floating around, all with slightly different format, based on whatever the users felt like using when they were updating it. Creating the data entry and search...Leave a comment:
-
NeoPa--I'll look into the QueryDef option. In the meantime, I'll try Mary's idea of saving as an accde. I've already hidden the navigation bar in the version I hang on the server--honestly, the people who use this database know even less about access than I do, so I doubt anyone's going to mess with the VBA code. I'm more concerned about them fooling around with the records already created, but I've tried hard (with all you guys' help) to give...Leave a comment:
-
NeoPa--that's great! The form looks exactly like I want it to when it opens. BUT, now the "previous record"/"next record" buttons don't work--I get the "can't go to specified record" error message. It seems like the command to go to a new record continues to execute, rather than being something that only executes once, when the form is opened. Code is currently:
...Code:Private Sub Form_Current() Call
Leave a comment:
-
OK, that worked! (you're right, the terminology was misleading--never would have guessed that)
Only problem now is that the form opens with the data from the very first record displayed--I could add a "go to last record" button, but I'd rather eliminate this step (and the possibility that someone could change the first record inadvertently) by having the form open up with blank fields in the controls, ready to accept a new...Leave a comment:
-
You're right--it didn't occur to me, but that line deletes the previous query in order to replace it with the new query with user-defined fields. I tried running it without that line and got the error message: Query "Search Database" already exists.
Looks like if I want to give users the ability to select which fields of the record the query displays, they are going to have to open the file in "edit" mode.Leave a comment:
-
Use data entry form to scroll through existing records
Hi, all! I'm a new user, looking to get some help on a form problem in MS Access 2007 I haven't been able to crack.
I've built a form that prompts users to enter new record data, then click "Update Database" to save the record to the table. Each entry creates a new record in the original table (all data goes to the same table).
I've used the button wizard to add a "previous record" and a "next... -
Execute Query in Read-Only Mode
Hi, all! After receiving a lot of help from ADezzi, Msquared, and NeoPa getting my ms access 2007 db functioning, I've run into another hitch that I haven't been able to solve. The database itself is saved on a MS Sharepoint server, and I would like users to be able to access it and execute the search query in read-only mode. In its current configuration, the query only "works" when the file is opened in "Edit" mode, and I've... -
Must be. But I can live with it as-is. Thanks again for all your help!
Joe...Leave a comment:
-
This allowed me to populate the controls with selections from the combo boxes in read-only mode, but when I clicked the "search" button, I got the error message: "Cannot update. Database or object is read-only."
Is there a way to allow the form to execute a search in read-only mode without having to update the database?
I'd already set the tab order, but this did not affect the...Leave a comment:
-
They are. I didn't think you could make an unbound combo box--can you?
BTW, I managed to solve the column-order problem. I realized the columns were populating the table in the order in which I had created them, so I just deleted all the boxes and built them back into the form, with associated macros, in the order that I wanted them to appear in the output table. Took a little while, but I'm happy with the results.
...Leave a comment:
-
Everything works! The form allows the user to select whichever output fields they want, then input criteria in text box/check box controls to filter whichever records they want to see.
The only hiccup I've encountered after hanging the file on our sharepoint portal is that when users open the file as "read only"--which I would prefer that they do, to avoid messing up the data--the drop-down menus on the form don't work....Leave a comment:
No activity results to display
Leave a comment: