Concatenate formatted date and text

  • Thread starter Thread starter Ryan
  • Start date Start date
R

Ryan

I'm writing this in VB code on the double click for a field in my
form. What I get when I run this is an error that says
"Type-decleration character does not match declared data type." I'm
trying to get something returned that says abc2004-04-07. Ahy help is
appreciated! Thanks!

Private Sub TextName_DblClick(Cancel As Integer)
Dim dtString As String

If Me.NewRecord Then
dtString = format$([date], "yyyy-mm-dd")
[TextName] = [artist_abv] & dtString

End If
End Sub
 
Hi Ryan,

Well, the 1st thing I see that bugs me is the use of a reserved word for a
field name ([date])...while a logical choice, I'd think about renaming it -
maybe adding something that describes the date - like BornOnDate...

Next, is it possible for [date] to be Null?

Finally, you could try forcing the type with CStr()...
dtString = CStr(format$([date], "yyyy-mm-dd"))
[TextName] = [artist_abv] & dtString

--

Regards,

John Boatner
MCSD, MCDBA, MCSE, MCSA, MCDST
J2 Technologies, Inc.
http://www.j2tech.com
 
Thanks for the tip John. I changed the name of the field to be
[show_date], and I'll also have to think about putting in logic to
cover the null situation as well.

I tried setting up the code as you suggested, and I still get an error
on a type mismatch. Is the VB date format a dt/tm like the access
one? I tried to strip out the time and just use the date portion and
still ended up with an error.

It doesn't seem that there's a problem with concatenating the two
until I try to format the date to be yyyy-mm-dd.

Thanks!
 
I'm still at a loss of how to get this to behave as I'd like. Any
help is appreciated. Thanks!!
 
Ryan,

The best I can tell is that there is something wrong with references in your
project. Try prefixing format$ with vba. like this vba.format$(....

--

Regards,

John Boatner
MCSD, MCDBA, MCSE, MCSA, MCDST
J2 Technologies, Inc.
http://www.j2tech.com


Ryan said:
I'm still at a loss of how to get this to behave as I'd like. Any
help is appreciated. Thanks!!

(e-mail address removed) (Ryan) wrote in message
Thanks for the tip John. I changed the name of the field to be
[show_date], and I'll also have to think about putting in logic to
cover the null situation as well.

I tried setting up the code as you suggested, and I still get an error
on a type mismatch. Is the VB date format a dt/tm like the access
one? I tried to strip out the time and just use the date portion and
still ended up with an error.

It doesn't seem that there's a problem with concatenating the two
until I try to format the date to be yyyy-mm-dd.

Thanks!
Click to expand...
Click to expand...
 
Hey John,

That did the trick! What's the use of the vba.? Thanks so much for the help!!

--Ryan
 
Every Access database has pointers to a number of libraries that are
required in order for Access to work. The reason for these references is to
allow for the reuse of common code: if you didn't use libraries, the Access
executable would be much larger (and you couldn't guarantee that certain
functionality would work identically in all Office products)

Sometimes one or more of the pointers gets broken: this can happen when you
copy the MDB file from one machine to another, or if you install new
software on your computer so that the file(s) which are being referenced are
no longer identical to what they were when the database was created. When
this happens, Access becomes unable to locate functions contained in any
referenced library, even in libraries for which the references haven't been
broken.

The Format (and Format$) functions are contained in the VBA library. When
you explicitly qualify the references like this, Access is usually able to
find the function (when you don't qualify them, it has to search through all
of the references, and having a broken reference prevents it from doing
that)

For far more than you could ever want to know about this problem, check out
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html
 
Back
Top