Replies

it is in fact stored as a date field… I fooled around with that idea awhile back but could not make it work consistently because when retrieving those records based on that key I used a form to trap the RateID and RateVersion and then lookup the key based on those variables, however some computers would be set to 2 digits and some would be 4 digit years and computers creating the key would always end being different from computers retrieving the records so I was hoping to find a way to determine the system date and notify user to set the global ddate settings all the same

I do it thru an update query:

SQL version is:
UPDATE RATES SET RATES.RateID = RATES!RateName & RATES!RateVersion & ” ” & RATES!Code
WHERE (((RATES.RateID) Is Null));

RateID = a text string of a name we give the rate
RateVersion = the date the rate was established… this is where it gets messed up… the rate version is a date formated field and when the person running the query has a system date that consists of a 2 digit year it will complete the query with a 2 digit year… then when a user who has a system date format as a 4 digit year tries to process something that requires it to find that key thru a “DLOOKUP” function I pass the RateID and RateVersion to the function from a form and it does not find it because of the date format mismatch.

Does this help ?

that did it thank you

I have now tried the second option in your reply Wendell… I believe at this point all is lost… Thank you for your help

Thank you Wendell, I tried the first part of your response and got an error every time it tried to import any forms from the backup… error stated it could not find first record or there were no related records… it gave me the option to hit “OK” and then attempted to import the next form… 4 out of approx 30 forms were eventually imported… I am attempting the decompile next but was curious about the errors… any idea what that error might relate to, seems strange to talk about records in an error associated with importing forms… all other parts of the database imported fine exept 2 VB modules which I could understand if the VBA is gone

Thank you both… learning a lot, especially how to spell… turns out after reading your replies I went back to the table where where the update is supposed to occur and I realized that I left the “C” off of the “EXEC” part of the name when I created the table, which actually explains several other issues I was having as well… Again thank you, this lounge is truly a great community

Wendll,

that is exactly what I am looking for thank you… if one or more of the detail records show a “yes” in the field then the whole report has to be labelled different than if none of the records show “yes” in the field… I wanted to accomplish this by an invisible label that gets set to visible if detail records contained “yes”… the Yes/No field is contained in the detail… I had not thought about the hidden sum field, i presume something as simple as If statement that sum is greater than or equal to 1, then set visible property to true, and do this in the OnPrint or OnFormat event of the header itself

Is this even possible… 2 hours searching google and I cannot find any place where a specific control within a report header is referenced from the detail section or vice versa

thank you, that worked perfect

Thank you Wendell… that was it… up and running again

this was a huge help… thank you

Yes i specifically go into the design view of the form and look at for example the “On CLick” Event for a button. The vba is actually missing. It happens only on this database, i have four other that were converted and am not having any issues with them.

Shouldn’t this line use vMon instead of vSun?

Hans,

Now I feel really stupid. Sorry to have taken your time on this. I appreciate the help though.

Thanks
Kevin

I really appreciate everyone looking at this. The one record that should pull is for the line:

“vInPut430TarsMon = Nz(DSum(“[Input 430 TARs]”, “tbl_Inputs_Outputs”, “[badge number] = ‘” & 503843 & “‘ and ([Activity Date] = #” & vSun & “#)”), 0)”

My apologies for copying the wrong line. The [Badge Number] field is a number field. I tried to take the single quotes out but it still gave me a zero. The right answer should be three.

The other thing that I noticed is usually the “AND” part of the statement is blue and in caps. In this case it is not. Is that because it is part of the overall string?

Thanks
Kevin

Could you attach a stripped down and zipped copy of the database that demonstrates the problem?

For now hold shift key when opening. The form called “frm_Report_Criteria” opens the parameters for the report “rpt_Op_Log_2”. If you select 11/21/2009, 400, 401, and 503843 in the drop downs respectively. This should find a record meeting that criteria in the “tbl_Inputs_Outputs” table. In the On Print event of that report you will find the dsum calc’s.