User Profile

metronj · Jan 2 '14, 09:41 PM

Yep, I get it. Nz to the rescue yet again lol. That works great.

Thanks zmbd, much appreciated as always!

Have a great new year everyone!

metronj · Jan 2 '14, 08:06 PM

Hi Guys,

Thanks for the response. I am writing the formulas simply as such:

Code:

DealerNet: FormatCurrency([Price]*[Disc])

Is there some way to write these so if there is an error it simply makes the field a null or zero perhaps?

Thanks for looking at this!

metronj · Oct 25 '13, 07:48 PM

Here was the solution:

1)Use an Update query to change the ETA Data Type to Text.

ALTER TABLE Table1 ALTER COLUMN [ETA Date] TEXT

2 Use an Update query with a IIF on the "update to:" line to determine whether or not the ETA date field is NULL. If it is NULL perform the function, if not just redisplay whatever date is in there.

Code:

IIf(IsNull([Table1]![ETA Date]),fCalcDate([ETA Date],[Port],[Prod

...

metronj · Oct 24 '13, 04:39 PM

Hi ADezii,

Same thing is happening. Here are some screenshots and the code I'm trying:

[IMGnothumb]http://i.imgur.com/OG2eqAS.png[/IMGnothumb]

Code:

UPDATE Table1 SET Table1.[ETA Date] = fCalcDate([ETA Date],[Port],[Prod Date]);

Code:

 Public Function fCalcDate(varDate As Variant, strPort As String, dteProd_Date As Date) As Date
    If IsNull(varDate) Then Exit Function

...

metronj · Oct 24 '13, 03:09 PM

Hi ADezii,

Thanks for your reply. I tried that and it set all of my dates to 12/30/1899 lol. I guess you are on the right track but something needs tweaking :)

Let me try to clear up the exact field names I'm using:
[ETA Date] = the date I'm trying to modify.
[Prod Date] = the date I have for sure. This is the date I want to add 50 or 70 days to if [ETA Date] is null and then put that calculated date into...

metronj · Oct 3 '13, 09:21 PM

Ahh.

This is Access 2010 and using the "exportwithform atting" option in the list.

Sorry about that.

metronj · Oct 1 '13, 03:20 PM

I figured it out, turns out you HAVE to have the "equal" sign in front of the whole thing:

Code:

="C:\Users\someuser\Desktop\Availability Report_" & Format(Date(),"yyyy-mm-dd") & ".pdf"

Hope this helps someone someday..

metronj · Sep 6 '13, 01:12 PM

Hi zmbd,

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

Code:

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

I can't thank you enough for your help.

Cheers!

metronj · Sep 5 '13, 01:24 PM

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?

Code:

datecalc: IIf(Eval(IsNull([ETA Date])) Or (Eval([ETA Date])=" "),([ETA

...

metronj · Sep 4 '13, 08:03 PM

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.

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Exporting to Excel not working if report has an #error in one of the fields.

Leave a comment:

Leave a comment:

Leave a comment:

Help with nested IIF Statement using DateADD.

Leave a comment:

Leave a comment:

How to Append Date to a .pdf export using macros?

Leave a comment:

Leave a comment:

Leave a comment:

Need Help to auto generate a Date in a report.