I want the Date() Now()

If I set the Default Field in the Table to -Date() each new field comes up nicely with a date (no time etc) which is what I want.

If however I want it to be based on an event eg Clicking a tick box VBA doesn't like Date() and I have to use Now().

On CLick

me.field = Now()

Is there any way of using date or can I fill in some parameters in Now(dmyyyy) or something that will make the field only a date field with no time information.

I know I can set the control's format but I want to be able to filter these fields by date easily.


Post your answer or comment

comments powered by Disqus

Can someone please help - it's driving me crackers !!!

Basically - I want to be able to track when a record was last modified - So that I can see if a record has not been reviewed within the last 30 days.

So far;

I have created a form, which adopting a great piece of code from this site, automatically creates an audit trail if any data on the form (individual record) is changed.

On this form is a field entitled 'Last_Modified' that automatically updates (with the 'Date ()') every time a change is made.

However - if the form is displaying a record that has not been amended/changed since it was created - this field is blank.

This means that if I run a query based upon the 'Last_Modified' date it would not recognise/identify the risk because there is no data entry - therefore a risk could that has not been modified since it's creation could potentially be unmonitored.

What I want to do is if the 'Last_Modified' field is Blank/empty I want the default data to be taken from a field in a table named 'Date_Raised'.

Do I do this through code? / using Before_Update?

Can someone please help?

Many thanks in advance.


How do I get a future date?
Monday to Friday is my working week.
I want the date of "today" next week
I am rubbish with funtions, so if it is a function please could you supply the whole funtion/ code that i can attach to a command button?

Thank you


I have put together a small Access database for my use at work. It has two tables and an input form.

In my input form 'InHouse Unlock Charges' is a field named "Description" that I have set to a ComboBox. I am trying to populate it with another table's field called "Products.Software".

In the Form Designer, I have done this by opening the Properties box for the ComboBox and selecting the following:
Row Source Type: Table/Query
Row Source: SELECT Product.id, Product.Software FROM Product;
When I run the form 'InHouse Unlock Charges', the ComboBox options show up correctly; however, if I try to input a new record, I get the message:"The value you entered isn't valid for this field.
For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits."Click OK and the message goes away.

After entering my data (which still works), I can go back to my updated Table "InHouse Unlock Charges" and see that the 'Description' field was not updated with the Text, but rather the SelectedIndex value.

I don't want the SelectedIndex value. I want the Text. What have I done incorrect?

(database is posted here)

I have designed a report which holds details of contracts. I want this report to have two purposes 1) the user can view a complete list of all contracts stored on the db and 2) they can select certain dates ie start and end to only produce a selection(mainly needed because contracts will need re-negotiated when they are just about to expire) Now I have the date function working correctly and the report returns the right records. I want to know how can I still display a complete list - still give the user the option because the parameter box will always ask for start and end dates - although this would work by entering the earliest and latest dates the user isn't going to know this. I want to give them two options 1) enter their dates (which is fine) and 2) view all WITHOUT ENTERING DATES

I don't know if I can do this but if not I will just create another report.

Thanks in advance


I am using SendObject in a Macro to send a table to a list of mail addresses (This list will not change so I have hard coded it into the macro).

My problem is that I want the subject line to read 'date'(in format DD MMM YY HH:MM) and then "Provision report"

I only tend to work in macros so I do not understand VBA that well.

Can someone please help me?


hey guys, i got a program that has a label with the Date.Now method to display the computer date and time.....i also have about 5 drop-down boxes for which i want to return segments of the date....for example...if the date read-out is now.....2/6/2007 7:10:46 PM.........i need one one of the combo boxes to display..."February"

I am not familiar with visual basic, however, is it possible to return the date in the combo box, something like. ComboBox1.Text = (##)...then run a 'Case' loop for the month (i.e. Case 1 = "January" Case 2 = "Feburary" Case 3 = "March"

any ideas????

I have this code running on a command button and it works fine. This is used to creat a 1 record DB file that a user can copy and give to other users (we do not have network access) so the next user can copy it into there database. As I was saying it works well as I have it here however, to make it a little easier to manage I want to add the Date now () to the file name that gets created but I'm having trouble adding it. This was what I started with.
Dim MyVar As String
MyVar = Format(Date, "mm-dd-yy")
DoCmd.SetWarnings False
If Dir("c:Finger PrintWetCleans" & Me.var2 & " " & Me.var5 & "*.accdb") = "" Then GoTo 100 ' file already there
Set fs = CreateObject("Scripting.FileSystemObject") ' moves the file for backup
fs.CopyFile "C:Finger PrintWetCleans" & Me.var2 & " " & Me.var5 & " & MyVar & .accdb", "C:Finger PrintBackups" & Me.var2 & " " & Me.var5 & " & MyVar & .accdb"
Kill "C:Finger PrintWetCleans" & Me.var2 & " " & Me.var5 & "*.accdb"
DoCmd.SetWarnings True
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''
I figured adding the date to the name would help the users but when deleting the file I could use the wild card name * and that would do the trick.
what an I missing

	DoCmd.SetWarnings False
If Dir("c:Finger PrintWetCleans" & Me.var2 & " " & Me.var5 & ".accdb") = "" Then GoTo 100 ' file already there
Set fs = CreateObject("Scripting.FileSystemObject") ' moves the file for backup
fs.CopyFile "C:Finger PrintWetCleans" & Me.var2 & " " & Me.var5 & ".accdb", "C:Finger PrintBackups" & Me.var2 & " " & Me.var5
& ".accdb"
Kill "C:Finger PrintWetCleans" & Me.var2 & " " & Me.var5 & ".accdb"
DoCmd.SetWarnings True
MsgBox "A Copy of this checklist " & Me.var2 & " " & Me.var5 & ".accdb will be created in C:Finger PrintWetcleans"
Dim wsp As Workspace ' This code creates the database
Dim dbs As Database
Dim strDBFile As String
strDBFile = "C:Finger PrintWetCleans" & Me.var2 & " " & Me.var5 & ".accdb"
Set wsp = DBEngine.Workspaces(0)
Set dbs = wsp.CreateDatabase(strDBFile, dbLangGeneral)
Set dbs = Nothing
Set wsp = Nothing
stDocName = "Qry-Nitride WetClean" ' qry - makes the table we want
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True
DoCmd.CopyObject "C:Finger PrintWetCleans" & Me.var2 & " " & Me.var5 & ".accdb", "WetClean Checklist", acTable, "WetClean
Checklist" ' copies the table we want to the output db
MsgBox " The copy has been created and can now be transfered. " ' msg to user file ready to go
stDocName = "Qry-Update Nitride Backup" ' copies the file to a backup table in this database
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True
stDocName = "Qry-Delete wetclean checklist" ' deletes the record
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

the date is appearing 11/27/2010
is this the default in Ms. ACCESS??
how can i change it to 27/11/2010
p.s I used the date() to be entered automatically after updating a textbox ,, i want to change it to 27/11/2010

Hello again. I am trying to create a query and here is what I have currently:

SELECT Count(*) AS Count_Action_Items, Sum(IIf(Actual_Completion_Date#1/1/2000#,1,0)) AS Sum_On_Time,
Sum(IIf(Target_Completion_Date < Date(Now,"mm/dd/yy") Or Actual_Completion_Date>Target_Completion_Date,1,0) ) AS Sum_Late, Find_Days_Late() AS Average_Days_Late
FROM [Special Requests]
WHERE ((([Special Requests].Date_Requested) Between [Forms]![Presage Search]![Start_Date] And [Forms]![Presage Search]![End Date]))
GROUP BY Find_Days_Late();

I bolded the part of this query that is giving me a headache. I want to check if the Target Completion Date is less than the current date. I thought the Date(Now) would work here but I am getting an error message saying 'Wrong number of arguments used with function in query expression "Sum(IIf(Target_Completion_Date < Date(Now,"mm/dd/yy") Or Actual_Completion_Date>Target_Completion_Date,1,0) ) AS Sum_Late".

Anybody have an idea what I am doing wrong here. Or maybe a better way to use the current date in an IIF statement


This one has me stumped. I am trying to change the date criteria in a select query if the day of the month is the 1st day or not. If it is the 1st day of the month then I want the date criteria to be between the 1st and last day of the previous month. If the current day is not the 1st day of the month then I want the date criteria to be between the 1st day of the current and the last day of the current month. I know how to do pull the between dates ranges but when I use an IIF or Switch the between dates range criteria does not work in a query.

How can I test the day of the month and use one date range if today is the 1st day of the month and use a different date range if today is not the 1st day of the month?

This is what looks like it should work but Access does not like the date ranges inside of a formula like an IIF or Switch. Using IIf(Day(Now())=1 is an easy way to test if today is the 1st day of the month.

IIf(Day(Now())=1,Between DateSerial(Year(Now()),Month(Now())-1,1) And DateSerial(Year(Now()),Month(Now()),0),Between DateSerial(Year(Now()),Month(Now()),1) And DateSerial(Year(Now()),Month(Now())+1,0))

Thank you in advance for your help!

I know this is a simple question but I just cant seem to get it right.
I have a form that is run by a query...the query filters out all records from the table that match the dayofweek I input..for instance, when form opens popup box asks what day? and I input Monday...all records with shipday monday appear in my form in datasheet view....

I want my date field in form to input the date on all records..my code that I entered says: on form load if Isnull(me.txtthisday.value) then
me.txtthisday.value = now() endif (I tried to put date() but my parenthesis always erased)

It puts the date on the first record, but I need every record to automatically have the date inserted when I open the form.

What am I doing wrong?
Thanks, Noreene

hi there
am new to access and i want to know how to use the function now in a query
like i want to know all the birthdays in the date base by using the function now.. firstly i did date part for the day and the month,, then in the criteria box of the day i inserted the function now () but dont know what to put.. any1 can tell me what to do pleaseeeeeeeeee ???????/

hey, little prob for improving my database..

In a box on the form i have the date automatically entered. however it is also possible for people to change the date for say the day before or the day after.

SO what i really want is for the date to still autoset (most of the entries will be for the current day) but i also want two little arrows on the box (like standard scroll bar style) to give me whatever date in the future, or past, that i require.

at the moment this can be done manually, but i want to make it as simple as possible so no gets to befuddled!!!

cheers for the help (if anyone can)


I'm using Access 2002. I have a subform that has a "Date" field.

In the Form Properties, in the "Order By" box, I put the word "Date"

It now sorts by "Date" - Ascending Order.
1) June 1st
2) July 10th
3) August 5th

I want the sorting order to show the newest entry first. Such as:
1) August 5th
2) July 10th
3) June 1st

How do I write the code to sort by descending date?


I have a series of information that I would like to be able to essentially count occurrences of a specific item, within a date range.

I have a database that contains columns for DATE, Quantity, and Model number, there is other information gathered but these are the ones I'm working with currently.

I can use the query wizard to create a simple query. If I select model, and quantity, then select next it asks if I want a detailed or a summary. I choose summary and from the summary options I can choose SUM of quantity.

This gives me a very simple result with 2 columns, a model number and a total quantity. This works perfectly, exactly the way I want it to. Now, to add the date range function.

The only way I can get it to prompt for a date range is to add the date column to the query, choose unique day and time, and then put Between [Start Date] And [End Date] in the criteria of the date field. This is all well and good, it prompts me beautifully for a start and end date, but the totals it returns are then broken apart by specific day which I don't want. All I need the date range to do is narrow down the query, not do any grouping, etc.

I feel like it should be simple but I'm not having much luck so I figured I'd ask the experts.

the date is appearing 11/27/2010
is this the default in Ms. ACCESS??
how can i change it to 27/11/2010
p.s I used the date() to be entered automatically after updating a textbox ,, i want to change it to 27/11/2010

I am sorting bunch of e-mail addresses, and the report generated the list in the following fashion:
email address1,
email address2,
email address3..... and so on.
What should I do so that the report generates them in one continuous line as shown in the following fashion:
email address1, email address2, email address3, ..........

The reason why I want to do this is beause I want to copy and past the entire line in the "to" field of an emailing application. If there is a smarter way for this, please tell me as I am a novice MS access user.


I have a problem with a date on my report. I want the user to type a date and select before or after. When they push the report button the report has to show the fields before or after the selected date.
I created some code in the On_Click event of the button that puts the right string in a textbox, like this :
[dateString] = "<#" & [TypedDate] & "#"

In the recordsource of my report i have set the criteria of the date to the textbox [datestring].
But this does not work, i get an error message saying that the criteria is incorrect or too complex.
When I type this string as criteria in the report myself, it works great.

Can anyone tell me what i do wrong or is there an alternative way to do this?
Any help would be greatly appreciated.
Thanks, Tim.

I have a splash screen form that is included in the startup options.

The splash screen opens central to the screen but at a reduced size compared to the whole screen. This bit is OK.

The problem is that when the splash screen times out I want the database window to maximize automatically.

If I create a macro to open the splash screen and then use the maximise action, it maximises the splash screen instead.

Can anyone help?

I want to populate a 2d array using data from a query. The first set of data for the array are dates in "ww" format......problem is that I want the array to be an Integer datatype. I guess the dates are variant data type.
Any one know how to change the dates to ints?

any help would be greatly appreciated.

Please consider the following situation.



On the form I have a box for both the ID and the name. I know them by name rather than ID, so when I type 'Bob' I want the form to autofill '1' into the customer_ID box.

Any help would be much appreciated. If possible I want this form to correspond to one table only. (Ideally I do not want customer names and customer ID to be on a different table)

Many thanks,


I have a warranty field that I am trying to create. I want the value of this field to be

Date_Purchase (Date/Time) + Warranty_Period (Number) = Warranty_Expires

e.g. 17/08/02 + 2 (years) = 17/08/04

I would like to do this calculation at the point of entering the record


I have a table with 2 Dates and an Integer. I want the Integer to be autoset to equal the amount of days between Date B and Date A. How do I do this?



I have a database with feils contained date/time. Currently I used Now() function as a default value and I want the date to be stored as dd-mm-yy hh:mm which I use it in format but if I click on the feilds the date shows dd-mmm-yy hh:mm:ss. I want it to save as 20-11-08 10:12 even when I click on the feilds I want it to appear as I mentioned.

Not finding an answer? Try a Google search.