473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Replacing empty fields with todays date

Hi guys, im a bit of a novice being thrown in at the deep end ! Using MS Access 97 i have a table with data that is updated once a day from an Excel spreadsheet. I need a way of identifying when data was added to this table so i have added an extra field (titled 'Date') and need to create a query that updates the empty field with todays date, so that every day this query will run and replaces any empty data in the field with todays date.

However im at a complete loss, can anyone help ?

Thanks
May 17 '07 #1
3 4168
Tig201
103 100+
You can use the “NZ” function to replace null fields with a set value
Expand|Select|Wrap|Line Numbers
  1.  Exp1: nz([AssignDate],Format(Now(),"mm/dd/yyyy"))
You can put this in the field location in the query (design view). This will create a new column Exp1 with the current date 05/17/2007 in any field that doesn’t currently have a value in the current column AssignDate and if there is a current value will leave it alone.
May 17 '07 #2
Cheers, that works fine.

Is there any way of updating the table with this data when running the query.?
May 17 '07 #3
Yes !

i did it..

thanks again
May 17 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Gerry Viator | last post by:
Hi all, I have a textbox were a time is typed in like: upto 4 numbers 1900 300 1000 1425 I would like as they type the text to show todays date plus the time they
2
by: Simon Pleasants | last post by:
I recently built a database to help keep track of shipments we are bringing into the country. Included in the database are a number of fields with dates for shipment, ETA etc. Each one of these...
2
by: Chuck | last post by:
Using a macro to output a query to an Excel file. Is there a way to automatically add todays date in the file spec? As: C:\Access Data\ & todays date.xls Chuck ....
8
by: Lyn | last post by:
I am trying to get my head around the concept of default, special or empty values that appear in Access VBA, depending on data type. The Access Help is not much (help), and the manual that I have...
5
by: Vayse | last post by:
In VB6, I would have used Date() That no longer works. How do I find todays date in VB.net?
2
by: rdraider | last post by:
Hi, We have a lame app that uses 2 datetime(8) fields, 1 stores the date, the other the time. example query: select aud_dt, aud_tm from orders results: aud_dt ...
2
by: Drum2001 | last post by:
Hello, I am having isues with the following: I have two forms, a MAIN FORM with a SUB FORM: Within the MAIN FORM, I have an unbound textbox (Date Format) and a command button. Onload, the...
8
by: Trev | last post by:
Hi Can anyone point me in the right direction here, I would like to open a table in access 2003 by date. I have an asp web page which needs to read data from a table with each days today's date...
1
by: archcool | last post by:
I want to display new empty text fields within the same form after selecting an option from a combobox. here is the form Filter by:todays date,from date ---- in a combobox when from date is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.