Creating an Input Field in an Update Query

[razorkat99]

My form contains the field Bill_Date, which may or may not be input during data entry. What I need is to run a query (I think) that allows me to input a specific date that will automatically populate all Bill_Date fields that are currently null with the date specified. Eventually this will end up on the front end where a biller will click on a link when they open the db, input the date into the field, and then process another report.

I have absolutely no idea how to even begin and was not able to locate any specific information in Help or here on the forum.

Many thanks.

 

[Colin@Toyota]

When you create your update query in design view, under the field you want to update, put [Enter Date] in the "UpdateTo:" line, and the "Criteria:" to "Is Null". By putting Enter Date in these brackets: [ ], this creates a prompt for user input. If you want your db to be a little more polite, you could try something like [Please enter a date]. One thing to note, you may want to put an input mask or format the field at the table level so that there will be no misstyping, or something like: [Please enter a date in the format mm/dd/yy].

This will filter the query to show only the records with no value in that date field, then will prompt the user to enter a date that will be applied to those records.

 

[razorkat99]

Thank you for the reply. I understand the entry, but I don't have a clue as to what's happening with the end result and Access Help is soooooooooooo not helpful. I set up the update query like Access said and input the date when prompted, but I can't find anywhere where the date is actually being updated with the date I specified. When I run the query it says 0 records will be updated, but I know there are records where the Bill_Date is null. I've never worked with update queries, so as stated previously, I am at a complete loss. I have my tables and my data entry input form, but that's it.

When I started the query in design mode I entered all of the columns I wanted from my table and pulled the correct data to test it. However, I noticed that when I changed it to an update query the only thing left was the Bill_Date column, which I assume is a correct end result for an update query. So, if my table has the Bill_Date column, why aren't the null records being updated?

 

[ajetrumpet]

So, if my table has the Bill_Date column, why aren't the null records being updated?

That's a good question...Personally, I would like to see what your SQL looks like...

 

[Colin@Toyota]

Check this out

See if it helps clarify anything for ya...

 

[ajetrumpet]

Who was that pointed at?? Ha ha!

 

[razorkat99]

Ok, I did the initial scanned read, but have moved on to getting my OnClose command to work properly for my switchboard at the moment and will come back to it when I can actually think. I work on a help desk team and half our internet customers have been up and down for almost two days and the phones are ringing non-stop!

 

[razorkat99]

Good News! I finally got it. I started over and followed an example on Microsoft's web site and everything works correctly. Same steps, correct result. Still not sure what the initial problem was though.