Serial Numbers (Again!) 1

[wreded]

Scenario:
Work orders arrive and pertinent data is put into an Excel spreadsheet. Each time a work order is received and receiving individual must create a work order number in the format "YYMMDD-" number of work order for the day (e.g., 01, 02, etc.). The spreadsheet thing drives me crazy! There are about 20 fields so the sheet is spread to the right a long ways. i can adapt the data to a form to get reports, etc., but i'm having issues with the work order number. i can massage the date to what i want and i finally got DCOUNT() to stop throwing errors, but when i try to push a button to simulate adding a new record i get just the suffix that i want, not the complete serial number (i think) i design for.

Private Sub Form_Load()
    Dim mDate, mDateCount As String

    mDate = Format(Date, "yymmdd")
    MsgBox "mDate = " & mDate, vbOKOnly, "mDate Value"
    
    mDateCount = DCount([TestDate], "table1", "[TestDate] = 'mDate'")
    MsgBox "Dcount Date = " & mDateCount, vbOKOnly, "Dcount Dates"


End Sub

Private Sub Command5_Click()
    

    If mDateCount = 0 Then
        Me.TestDate = mDate & "-" & "01"
    Else
        Me.TestDate = mDate & "-" & mDateCount
    End If

    MsgBox "mDateCount = " & mDateCount, vbOKOnly, "mDateCount"
    MsgBox "mDate = " & mDate, vbOKOnly, "mDate"

End Sub

Test database has 1 field, string [TestDate]. Form has 1 field [TestDate] and one button "Command5" with the code above attached to it. Pointers please?

Thanks,
Dave

 

[TheAceMan1]

How are ya wreded . . .

Try the following in your button:

[blue]   Dim mDate As String, mDateCount As String, DL As String

   DL = vbNewLine & vbNewLine
   mDate = Format(Date, "yymmdd")
   mDateCount = Format(DCount([TestDate], "table1", "[TestDate] = 'mDate'") [purple][b]+ 1[/b][/purple], "00")
    
   MsgBox "mDate = " & mDate & DL & _
          "mDateCount = " & mDateCount & DL & _
          [purple]"New Serial No. = " & mDate & "-" & mDateCount[/purple], _
          vbOKOnly[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

Anyway, I'd use this sort of DCount:

DCount("[TestDate]", "table1", "[TestDate] Like '" & mDate & "*'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

wreded . . .

I've been looking at this and if you notice ... deleting one or more records could screw up the sequence. You may want to consider [blue]DMax[/blue].

Also, [blue]PHV[/blue] already has the correct code for DCount if [TestDate] is a string. If testdate is in fact a Date [blue]datatype[/blue] then the correct code for DCount would be:

[blue]DCount("[TestDate]", "table1", "[TestDate] = #" & mDate & "#")[/blue]

[blue]Your Thoughts? ...[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[wreded]

Thanks!

AceMan your code works as i thought mine should have. i am storing the mDate as a string, i've never really understood how MS treats dates. Too much to learn. i'm off on another adventure as a geospatial mapper now, just trying to get the office to run right so the process is easier to manage.

i like Your input too PHV, why check for equivalency when a check for "LIKE" works just as well.

Both more tools in the toolchest.

i am puzzled as to why, when i set the variables in "On Form Open" they don't seem to stay in focus for the button push.

Stars all around!

Thanks!

Dave

 

[TheAceMan1]

[blue] ... i've never really understood how MS treats dates. ...
i am puzzled as to why, when i set the variables in "On Form Open" [purple]they don't seem to stay in focus[/purple] for the button push.[/blue]

For Dates look here: Using Dates and Times in Access

And for variables: Scope of variables in Visual Basic for Applications

[blue]Cheers![/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SkipVought]

The article never explicitly states that dates are numbers, and what is displayed is a format.

A sub article might provide insight:


ACC: Storing, Calculating, and Comparing Date/Time Data

http://support.microsoft.com/default.aspx?scid=kb;en-us;130514

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wreded]

Thank You again! Interesting reading.

Dave