DatePart Function 3

[NewfieSarah]

Hey all I have a 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)
I looked up the code for a right function and it is suppose to work on a string, so I tryed it on this. My CODE:
Dim Year As String
Year = RIGHT(Num, 2)
I obtained a type mismatch WHY???

 

[PHV]

Why not simply this ?
strYear = Format(strDate, "yy")

Note: avoid to use keyword (like YEAR) as variable name ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NewfieSarah]

yes I have realized that now I have changed it to MYYEAR however the code that you presented my with PHV is a format that is not what I amlooking for really. I need the 05 to use to concantenate together with my record numbers. With this code I am getting a type mismatch

MY CURRENT CODE:

Dim Num As String
Dim count As String
Dim MYYEAR As String
Dim NUMBER As String

count = DCount("*", "Building")
count = count + 1
Num = Str(DatePart("yyyy", STRDATE))
MYYEAR = RIGHT(Num, 2)
' NUMBER = MYYEAR + " - " + count

PERMIT.SetFocus
PERMIT.Text = MYYEAR (NUMBER)

 

[PHV]

Something like this ?
Dim myCount As Long

PERMIT.SetFocus
myCount = 1 + Nz(DCount("*", "Building"), 0)
PERMIT.Text = Format(strDate, "yy") & " - " & myCount

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NewfieSarah]

OH MY you are a life saver thanks for all the help. AMAZING! Hey have you seem some of my other post? Maybe you could help me with some of those!! Thanks again so much.

 

[NewfieSarah]

So I have had a bit of time to look at this code, and it is really good. however I am just looking at the add and delete buttons, once I add and delete I can added 2 records 1002 and 1003 and then I deleted 1002 and I could add 1003 again. so I would have 2 1003 that is not really a good thing now is it?? So how can I solve this problem?? Thanks

 

[PHV]

Have a look at the DMax function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NewfieSarah]

So your opinion is the DMax function is the best on to use??

 

[PHV]

The less worst if you don't have 2 users willing a new PERMIT at same time ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NewfieSarah]

you wouldnt have any idea how I can use it in my code do you?? I am not real sure on this function??

 

[PHV]

Typed, not tested (assumptions: the table Building has a field named Permit formatted like 'yy - #'):
Dim myCount As Long
PERMIT.SetFocus
myCount = 1 + Nz(DMax("Val(Mid(Permit,6))", "Building"), 0)
PERMIT.Text = Format(strDate, "yy") & " - " & myCount

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NewfieSarah]

no my building table is not formated it is formated on the form only. in my table my PERMIT field is text. I will give it a try. Thanks

 

[NewfieSarah]

so I tryed that code,and I got an error for a data type mismatch in criteria expression. So what does that mean, how can I fix that??

 

[PHV]

Which criteria ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NewfieSarah]

i dont know I added the code that you posted that wasnt tested and that was the error I got, nothing to debug or anything!!! so it has somehting to do with that code. any ideas what is worng with it?? Thanks so much

 

[PHV]

You may try this:
myCount = 1 + Val(Mid(DMax("Permit", "Building") & "", 6))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NewfieSarah]

Well it got ride of the error, but now I only get 05-3 not 05-10003, and they are not increasing or decreaing on delete. just 3 every time.Thanks

 

[NewfieSarah]

Hey PHV, I was wondering if you think it was a good idea to validate, the number if it already exists then add one more?? Since when I delete it can add the same number again, it should work to add a new record, and check to see if it exists. So what do you think?? How would I do that?? Thanks for all the help

 

[PHV]

Have a look at the links I've posted in this thread:
thread702-1145516

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NewfieSarah]

yes but they dont help, I am not looking for autonumber help. I was just wondering now that i have my calculated number field, how would I be able to validate it? so that when I add a new record. That if the number is suppose to be 10002 and it is already in my database then have 10003 show up instead?? that way I only have one 10002 instead of two... Hey did you see my other post about default populated data?? Have any ideas on that??Thanks