Dmax function was working but isnt anymore

[sonny123]

I have Main table and one form with about a dozen fields

I have field which is counter of records which 'opened' on a given date

The openDate is a field in the form and in the table

I originally got the function to work like this

Private Sub InputBY_Exit(Cancel As Integer)
Me![3DigitCode] = Nz(DMax("[3DigitCode]", "[Main]", "[DateOpened] = #" & Forms!Main!DateOpened & "#"), 0) + 1
End Sub

and all was Dandy, But since then ive added some more fields to the table changed the tab order of the form and added some more tables (which are basically to look up and assign Id numbers for various descriptions)
Also I renamed my Table fields to distinguish them from the form fields( i read this was a good idea)
But my Dmax function doesnt work anymore

First: I thought that my additions and changes had caused the problem so I went back to a copy of the mdb file that I saved off just before I figured out the syntax for the dmax function, I added the code (Changing the field names accordingly)as above like I'd done before but It didnt work a 2nd time

Next I tried this:

Private Sub InputBY_Exit(Cancel As Integer)
Me![3DigitCode] = Nz(DMax("[3DigitCode]", "[Main]", "[DateOpened] = Date()"), 0) + 1
End Sub

That sort of works but the counter counts the records that were Created on GetDate() not the records that were opened on DateOpened


Next: I thought I'm gonna have to start from scratch so
I created a new mdb with just 3 fields: Date, text, and counter.

Private Sub Text_Exit(Cancel As Integer)
Me![Counter] = Nz(DMax("[Counter]", "[Main]", "[Date] = #" & Forms!Main!Date & "#"), 0) + 1
End Sub

this wouldnt work, i just kept getting 1 as the counter result

but this worked

Private Sub Text_Exit(Cancel As Integer)
Me![Counter] = Nz(DMax("[Counter]", "[Main]", "[Date] = Date()"), 0) + 1
End Sub

but is not what i want to count.

im rather green when it comes to access and becoming more confused by the minute

 

[gemma-the-husky]

are you by any chance storing the time along with the date? - that would have an effect

 

[sonny123]

Im sure all the datatypes in the form and the table are set to shortdate

 

[gemma-the-husky]

does the data look correct in the table

i am not 100% sure without trying it, but using the dates the way you are may cause the date to be treated as a US date.

if you have an unambiguous UK date - say 13/3/11 - then it will work as a UK date - but 12/3/11 would be treated as 3rd December.

As I say - not sure, but its always a possibility when handling dates

 

[missinglinq]

I originally got the function to work like this

Me![3DigitCode] = Nz(DMax("[3DigitCode]", "[Main]", "[DateOpened] = #" & Forms!Main!DateOpened & "#"), 0) + 1
End Sub

and all was Dandy, But since then ive...renamed my Table fields

If you changed the name of your Fields in the Table then you have to change the name of the Field you're running DMax() against! Your code still shows 3DigitCode as being both the Control name on the Form and the Field name in the Table.

Me![3DigitCode] = Nz(DMax("[3DigitCode]"

The second 3DigitCode above has to be whatever you changed the Field Name to.

Linq ;0)>

 

[sonny123]

yes the data looks fine in the table (UK date format DD/MM/YYYY)

I kind of thought it maybe that the data (when dealing with the current record) wasnn't being counted as being in the table because all the form data hadnt been entered

but that wouldnt explain why after making multiple entries with the same Dateopened why dmax still returns a value of 1

 

[sonny123]

If you changed the name of your Fields in the Table then you have to change the name of the Field you're running DMax() against! Your code still shows 3DigitCode as being both the Control name on the Form and the Field name in the Table.

Me![3DigitCode] = Nz(DMax("[3DigitCode]"

The second 3DigitCode above has to be whatever you changed the Field Name to.

Linq ;0)>

Yes I changed the field names in the DMax code syntax as well

It didn't work either with my simple little 3 field DB that i did from scratch

EDIT: which is why I am so confused

 

[sonny123]

I think I understand what the issue is, but dont know how to fix it.
If the 3DigitCode keeps returning a value of 1 then It must be reading the DateOPened field on the current form as Null, because thats the only way it could return a value of 1.

 

[sonny123]

I fixed the problem, I dont know what it was, I had a working version mdb on my work PC. I emailed it to myself, but when i got home it didnt work on my machine at home I thought maybee it was 2003 / 2007 thing

after a google or two I used the Detect and Repair tool which reset all my regitry keys to the default, and Hey Presto! it works again

Okay so is there a mark thread solved button thingy anywhere???