Record Count?

[NewfieSarah]

Hey all, I was wondering if anyone might know how to obtain a reocrd count, I need to create a PERMIT number based on the number of records in my table. So I need the number of records in my table plus 1, which will then give me half of my permit number, I will then add the 2 last digits of the current year and add a "-" I think I have the date done, but I dont know how to obtain the number of records in my table? Can anyone help? Thanks

 

[KenHigg]

.
.
.
.
Dcount()

 

[NewfieSarah]

that is kind of blunt, and to the point I guess! Okay so I can use the Dcount, but how do I use it? Dcount(WHAT IS THE CODE FOR HERE) I know what they suggest but I dont know what to put in there? That is why I am asking for help with this situation. Thanks

 

[KenHigg]

Hi Sarah, Sorry to be so blunt . See if this helps:

Link

 

[Pauldohert]

"WHAT IS THE CODE FOR HERE" - Look in the help file?

 

[Brianwarnock]

Your query will be

SELECT Count([uniqueid]) AS cnt, ([cnt] & Right(Year(Date()),2) & "-") AS permit
FROM yourtable;

Brian

 

[NewfieSarah]

So I can use the DCount to find the number of records in my table by using this:
count= DCount("*", SEWER) Where * counts all the records, in the table and SEWER is the name of my table. HUMM I got an error for my table name: It said variable not defined. Why is that?

 

[KenHigg]

Actually, I think this is a bad way to come up with a perimt number as the number of records could go up OR down...

 

[NewfieSarah]

Well that is okay. That is what I need to do, and need help on. Sorry if I am being blunt but if that is what my boss wants then that is what he is going to get, however I have talk to him about that and it is fine with us. Thanks

 

[Brianwarnock]

So I can use the DCount to find the number of records in my table by using this:
count= DCount("*", SEWER) Where * counts all the records, in the table and SEWER is the name of my table. HUMM I got an error for my table name: It said variable not defined. Why is that?

I think the table name should be in " ".
Oh and i agree with Ken's last post

brian

 

[NewfieSarah]

Thanks it does have to be in " " it works awsome thanks alot.

 

[NewfieSarah]

Hey guys. I was wondering if you knew how I can get a part of my datepart function out, I have taken the year from the the date with the date part function and now I need only the 05, so what can I do to get it out? Thanks MY CODE: Dim Num as string Num = DatePart("yyyy", STRDATE)

 

[neileg]

("yyyy", STRDATE) should be ("yy", STRDATE) to get just 05

You need to upgrade to BOSS 2.0. The count of the number of records will only give you the next number so long as the records still exist. If you have issued 83 permits and have 83 records, the next will be 84. If you have deleted one of the records, accidentally or deliberately, then the count will give you 82 and you will be trying to issue number 83 again. It is better to store the permit number in the record and use DMax() to retrieve the largest number in this field, and then add 1. Your boss doesn't need to know that you've done it the right way (unless you have BOSS 5.1 in which case all deception is useless!).

 

[NewfieSarah]

That is what i though, I got an invalid call, procedure or argument. I have heard about the DMAX() but I dont understand it. I cant get the code right.Could you help me out with it?? Dont worry about the boss. Not that kind of Boss. LOL (LAUGH OUT LOUD) It would be great if you could help though!

 

[NewfieSarah]

I got it to work, This is what I have now! Works like a charm!
Dim myCount As Long
PERMIT.SetFocus
myCount = 1 + Nz(DCount("*", "Building"), 0)
PERMIT.Text = Format(STRDATE, "yy") & " - " & myCount

 

[KenHigg]

Cool. Glad you got it to work. May I suggest that as an alternate, you store the a last permit number issued value in a table and use it to arrive at the next permit number. (This is just the way I would do it, others may have a better solution...)

 

[NewfieSarah]

okay, so I think that is a good idea. but I am not really sure on all the details that you are talking about so can you let me know a bit more, Now that i have had a chance to see the add and delete I understand a bit more where you are coming from. So what do you think is a good idea??

 

[KenHigg]

I sometimes create a table and call it something like tblSytem to store values like the one you need to keep up with. In it I have an autonumber pk, a text fld called something like 'valueName' and third text fld called something 'systemValue'.

So in this table do a new record. In the valueName fld do something like "currentPermit' and in the systemValue fld put what ever want the permits to start at, say maybe '100'.

So when add a new permit, you grab this value to use in the permit table and at the same time you increment it by 1, etc.

Hope all this makes sense...

 

[NewfieSarah]

Thanks for the input but I dont think that is an option for me at this time. So Many people are telling me I should use the DMax funciton however I have got nothing back on how I can use it, and I have see the syntax for it but it is just not clicking for my problem! Thanks

 

[KenHigg]

You're welcome, and good luck!