Need Help to auto generate a Date in a report.

[metronj]

Hello All, I'll try to be as brief and concise as possible with this question:

I have 2 date fields:

#1 is called "Prod Date" and it will ALWAYS be populated. ex; 1\1\2013.

#2 is called "ETA Date" and may or may not be populated. ex; it may be NULL OR contain a valid date such as 2\1\2013.

What I need to do is this: I have my queries set up to filter out records based on certain criteria (nothing to do with the dates). Now when I go to run the report I need to take a look at the "ETA Date". If it is populated with a date then I don't want to do anything other than put that date on the report. However if the field is NULL I want to take the "Prod Date" field value, add 75 days to it, then put that value on the report under the ETA Date column. I DO NOT want to manipulate the original Prod Date field as I need to display that on the report as well.

How can this be done and where would I put the code or expression? I have tried doing some other things I found here but I can't get it to work at all.

If "eta date" = null then eta date = prod date + 75 days (in simple english :) )

Thanks to any and all who can help me with this problem. I greatly appreciate it.

[zmbd]

Look at the NZ() function.
You can either use it as a calculated field in your query or as the control source for a text box on the report. I personally like it in the query as it is easier to maintain IMHO.

Nz Function
NOTE: This will only work for a truely "null" value. If for some reason this isn't working (because the field is a zero-length string) then you'll need to look at the IIF() function instead IIf Function Use the criteria so that [field]&""="" will check for null and zero-length.

[metronj]

Thanks for the reply zmbd...I should have noted that I only have a background in programming and if you can post the actual code and where to put it I would GREATLY appreciate it. We do not have an IT department in my office and I am trying to do this on my own.

Many Thanks.

[zmbd]

metronj:
That is really tuff when there's no support structure to even provide a little moral support.

Both of the links I gave in the last post provide some examples of VBA and the basic syntax you will need to use the functions; however, I don't think you need to use VBA to solve this issue.

What I would try first is in your query add a third field, this will be a calculated field ( "zdatecalc" )

Where the field name would normally sit you would do something like this:

zdatecalc: Nz([datefield_to_check],DateAdd("d",75,[datefield_to_add_days_to]))

If for some reason you are not getting the expected value, check that the field [datefield_to_ch eck] doesn't have a zero-length string... once again you can do this with a calculated field in the query, just use the IIF() as given in my last post in place of the NZ(), use the criteria as given as last time, and then set the true and false branches.

Some will tell you to do this at the table level, it's not considered best practice, even if it is possible in Access. Also don't do it in the table as it will start to bog you database down.

In anycase, when you use the query as the recordsource for your report you will now have the field named "zdatacalc" to add to the details section.

I should point out, respectfully, Bytes is neither a code writing nor homework service. Think of us as a teacher of how to fish so that you can eat tomorrow.

As you mention that you have very little background with Access; the following website will provide some easy to follow basics with a smattering of advanced topics: MS Access 95 Tutorial The site is very old in the timeline of software and the author has some hangups with VBA; however, ignoring that bias and the section on switch-boards (which are nolonger supported in ACCDB files ACC2010) the remaining Fundamentals will hold true. Of Note: VBA and MACRO are NOT the same nor are they interchangable - each have their role; however, VBA is much more robust.

I also suggest that you work your way thru the articles found here[*]Microsoft Access / VBA Insights Sitemap these will help you with a lot of questions, especially the one covering > Database Normalization and Table Structures.

Finally do yourself a HUGE favor and read thru and setup your VBA editor as given here: > Before Posting (VBA or SQL) Code. The option explicit and the compile steps will save you many a headache.

Peace Be With You.
-z

[metronj]

Hi zmbd and thanks again for replying. I totally understand that this isn't a code writing service and I may just end up having to pay for someone to do this for me. :(

This is the best I have been able to come up with and it doesn't work. Can you please tell me if I'm on the right track or (even better), please clean it up for me so that it works?

datecalc: IIf(Eval(IsNull([ETA Date])) Or (Eval([ETA Date])=" "),([ETA Date])=DateAdd("d",75,([Prod Date])),[ETA Date])

Using your advice from the previous post you can see I'm trying to compare my date field to either NULL or " " (space)..If true I am trying to set that field to the value of my production date field + 75 days and if false I just want to leave the field alone and display the date that is in there.

Many MANY thanks.
-m

[zmbd]

Well, hiring someone may be a longer term solution. However, with the link to the basics I sent you and your programing background (which you haven't told us much about... you can PM me with that info you would like :) ) you should be up and running in short order.

I'll take a look at your solution more indepth in just a second. I have some projects going in the lab that require me to babysit them... in the meantime, try the NZ() code I posted in #4. You should only need to change the names in the fields to match your tables.

One quick note: (Eval([ETA Date])=" ") is checking for a single 'space' charactor, not an empty or zero-length string.I would go back to your table design, check that the field is set to "Date/Time" data type (BTW: the comparison with a string in a date field may produce the #ERROR message in the field). If the user deletes or enters a space into such a field the NZ() code should still work, where your's will fail.

As I noted earlier, I have a few projects that need to be micromanaged today so I may not reply back for awhile; however, there are other experts and peers here that may also offer some help in my absence. - I haven't left you hanging... I just need to go make the company some money!

[metronj]

Hi zmbd,

After a bit more trial and error I was able to get the code snip you pasted to work:

zdatecalc: Nz([datefield_to_check],DateAdd("d",75,[datefield_to_add_days_to]))

I can't thank you enough for your help.

Cheers!

[zmbd]

Hurray!
If you'll select the "best answer" it will help the next person reading the thread to hopefully find the solution for their question too!

and off to the next topic/task... Fridays I the kids... I have more work at home than at work!