Code will not resolve: rst = Nothing

[saraqpost]

Set rst = "SELECT tblActData.Dept, tblActData.Amount,
tblActData.COAKey, tlkpCOA.Number, " _
& " tlkpCOA.Account, tblActData.Subsidiary,
tblActData.FiscalYr, " _
& " tblActData.FiscalMonth, " _
& " #" & "DateSerial([tblActData.FiscalYr],
[tblActData.FiscalMonth],1) # " _
& " FROM tblActData INNER JOIN tlkpCOA ON tblActData.COAKey =
tlkpCOA.COAKey " _
& " WHERE tblActData.COAKey = " & lngCOAKey _
& " AND tblActData.Subsidiary = '" & strSubsidiary & "'" _
& " AND #(DateSerial([tblActData.FiscalYr],
[tblActData.FiscalMonth], 1) #" _
& " Between DateAdd " & "('m', -12," & "#" & dtmEndDate & "#)"
_
& " And #" & dtmEndDate & "#" _
& " ORDER BY tblActData.Dept, tblActData.FiscalYr,
tblActData.FiscalMonth;"

I'm trying to use the above code to count the records so if there are
no matches with the criteria, I do not show the listbox, but tell the
user, instead.

The code gives me "424 - Object Required" message. And in the
Immediate Window I see rst = Nothing,

I struggled to get the code above to display - the # around the dates
was challenging, but I put the above in a strSQL and it looked ok (but
obviously is not). When I put the same code into set rst, I get the
message.

strSQL: (immediate window)
SELECT tblActData.Dept, tblActData.Amount, tblActData.COAKey,
tlkpCOA.Number, tlkpCOA.Account, tblActData.Subsidiary,
tblActData.FiscalYr, tblActData.FiscalMonth, #DateSerial
([tblActData.FiscalYr],[tblActData.FiscalMonth],1) # FROM tblActData
INNER JOIN tlkpCOA ON tblActData.COAKey = tlkpCOA.COAKey WHERE
tblActData.COAKey = 433 AND tblActData.Subsidiary = 'Parent Company'
AND #(DateSerial([tblActData.FiscalYr], [tblActData.FiscalMonth], 1) #
Between DateAdd ('m', -12,#6/1/2010#) And #6/1/2010# ORDER BY
tblActData.Dept, tblActData.FiscalYr, tblActData.FiscalMonth;


Can anyone help?
Thanks in advance -
Sara

 

[Tom van Stiphout]

On Sat, 31 Oct 2009 18:54:46 -0700 (PDT), "(e-mail address removed)"

You have at least two problems:
You should have:
dim rst as dao.recordset
dim sql as string
sql = "select etc etc"
set rst = currentdb.openrecordset(sql)

Also make sure you have:
Option Explicit
at the top of EVERY code module, and set this to be the default in the
Options screen.

Second, the sql statement is incorrect, which you can find out if you
paste it in a new query in SQL view, and switch to design view.
It should be something like:
sql = "select etc etc " & _
"where #" & DateSerial(x, y, z) & "# between #" & _
dateadd('m',-12,dtmEndDate) & "# and #" & _
dtmEndDate & "#"
The point to keep in mind is that you are concatenating a string out
of several pieces.

-Tom.
Microsoft Access MVP

 

[saraqpost]

On Sat, 31 Oct 2009 18:54:46 -0700 (PDT), "(e-mail address removed)"


You have at least two problems:
You should have:
dim rst as dao.recordset
dim sql as string
sql = "select etc etc"
set rst = currentdb.openrecordset(sql)

Also make sure you have:
Option Explicit
at the top of EVERY code module, and set this to be the default in the
Options screen.

Second, the sql statement is incorrect, which you can find out if you
paste it in a new query in SQL view, and switch to design view.
It should be something like:
sql = "select etc etc " & _
  "where #" & DateSerial(x, y, z) & "# between #" & _
  dateadd('m',-12,dtmEndDate) & "# and #" & _
  dtmEndDate & "#"
The point to keep in mind is that you are concatenating a string out
of several pieces.

-Tom.
Microsoft Access MVP

  Set rst = "SELECT tblActData.Dept, tblActData.Amount,
tblActData.COAKey, tlkpCOA.Number, " _
       & " tlkpCOA.Account, tblActData.Subsidiary,
tblActData.FiscalYr, " _
       & " tblActData.FiscalMonth, " _
       & " #" & "DateSerial([tblActData.FiscalYr],
[tblActData.FiscalMonth],1) # " _
       & " FROM tblActData INNER JOIN tlkpCOA ON tblActData.COAKey =
tlkpCOA.COAKey " _
       & " WHERE tblActData.COAKey = " & lngCOAKey _
       & " AND tblActData.Subsidiary = '" & strSubsidiary & "'" _
       & " AND #(DateSerial([tblActData.FiscalYr],
[tblActData.FiscalMonth], 1) #" _
       & " Between DateAdd " & "('m', -12," & "#" & dtmEndDate & "#)"
_
       & " And #" & dtmEndDate & "#" _
       & " ORDER BY tblActData.Dept, tblActData.FiscalYr,
tblActData.FiscalMonth;"

I'm trying to use the above code to count the records so if there are
no matches with the criteria, I do not show the listbox, but tell the
user, instead.

The code gives me "424 - Object Required" message.  And in the
Immediate Window I see rst = Nothing,

I struggled to get the code above to display - the # around the dates
was challenging, but I put the above in a strSQL and it looked ok (but
obviously is not).  When I put the same code into set rst, I get the
message.

strSQL: (immediate window)
SELECT tblActData.Dept, tblActData.Amount, tblActData.COAKey,
tlkpCOA.Number,  tlkpCOA.Account, tblActData.Subsidiary,
tblActData.FiscalYr,  tblActData.FiscalMonth,  #DateSerial
([tblActData.FiscalYr],[tblActData.FiscalMonth],1) #  FROM tblActData
INNER JOIN tlkpCOA ON tblActData.COAKey = tlkpCOA.COAKey  WHERE
tblActData.COAKey = 433 AND tblActData.Subsidiary = 'Parent Company'
AND #(DateSerial([tblActData.FiscalYr], [tblActData.FiscalMonth], 1) #
Between DateAdd ('m', -12,#6/1/2010#) And #6/1/2010# ORDER BY
tblActData.Dept, tblActData.FiscalYr, tblActData.FiscalMonth;

Can anyone help?
Thanks in advance -
Sara- Hide quoted text -

- Show quoted text -

Thank you for the response, Tom.
I do always have Option Explicit, and I did have the dimensioning of
the variables. I added the "DAO" - which I had tried before but took
out since it didn't make a difference. Admittedly, I am unclear on
DAO, ADO and the Access versions.

I tried to adjust my sql statement according to your hints (see below)
and I'm getting "invalid procedure call or argument".

I have taken the string apart and it seems ok until the "Between".
Since dtmEndDate is a date, I've tried many ways to put the hash marks
around it (even though you didn't have them - I tried it when I
failed, since they are present after the "AND"). Still not working.

I tried to put this SQL into a query - that didn't work, and I went
back to my original query to "re-create" the VBA code and this is what
I end up with - pretty much the same, adding in your ideas on the
"#". But still, no luck. I know it's a little complicated, but I've
felt I've broken it down and gotten each portion as it should be
(obviously not!)

If I take the double Quotes away from the 'm', I get: Syntax error on
the compile. No matter what I do to try to work with that segment of
the code, I get a compile error.

Can you help?


StrSql = "SELECT tblActData.Dept, tblActData.Amount,
tblActData.COAKey, tlkpCOA.Number, " _
& " tlkpCOA.Account, tblActData.Subsidiary,
tblActData.FiscalYr, " _
& " tblActData.FiscalMonth, " _
& " #" & "DateSerial([tblActData.FiscalYr],
[tblActData.FiscalMonth],1) # " _
& " FROM tblActData INNER JOIN tlkpCOA ON tblActData.COAKey =
tlkpCOA.COAKey " _
& " WHERE tblActData.COAKey = " & lngCOAKey _
& " AND tblActData.Subsidiary = '" & strSubsidiary & "'" _
& " AND #(DateSerial([tblActData.FiscalYr],
[tblActData.FiscalMonth], 1) #" _
& " Between # " & DateAdd("'m'", -12, dtmEndDate) _
& " # And #" & dtmEndDate & "#"


Much appreciated -
Sara

 

[Stefan Hoffmann]

hi Sara,

I tried to adjust my sql statement according to your hints (see below)
and I'm getting "invalid procedure call or argument".

In your original post, the error was caused by the Set StrSql = "..".
Set is only necessary and valid for object variables. So ensure that you
don't have it in your actual code.

StrSql = "SELECT tblActData.Dept, tblActData.Amount,
tblActData.COAKey, tlkpCOA.Number, " _
& " tlkpCOA.Account, tblActData.Subsidiary,
tblActData.FiscalYr, " _
& " tblActData.FiscalMonth, " _
& " #" & "DateSerial([tblActData.FiscalYr],
[tblActData.FiscalMonth],1) # " _
& " FROM tblActData INNER JOIN tlkpCOA ON tblActData.COAKey =
tlkpCOA.COAKey " _
& " WHERE tblActData.COAKey = " & lngCOAKey _
& " AND tblActData.Subsidiary = '" & strSubsidiary & "'" _
& " AND #(DateSerial([tblActData.FiscalYr],
[tblActData.FiscalMonth], 1) #" _
& " Between # " & DateAdd("'m'", -12, dtmEndDate) _
& " # And #" & dtmEndDate & "#"

First of all, use table alias names and reformat it, e.g.

StrSql = "SELECT D.Dept, D.Amount, D.COAKey, C.Number, " & _
"C.Account, D.Subsidiary, D.FiscalYr, D.FiscalMonth, " & _
"DateSerial([D.FiscalYr], [D.FiscalMonth], 1) " & _
"FROM tblActData D " & _
"INNER JOIN tlkpCOA C ON D.COAKey = C.COAKey " & _
"WHERE D.COAKey = " & lngCOAKey & " " & _
"AND D.Subsidiary = '" & strSubsidiary & "' " & _
"AND DateSerial([D.FiscalYr], [D.FiscalMonth], 1) " & _
"BETWEEN " & _
Format(DateAdd("m", -12, dtmEndDate), "#mm/dd/yyyy#") & " " & _
"AND " & Format(dtmEndDate, "#mm/dd/yyyy#")

mfG
--> stefan <--

 

[saraqpost]

hi Sara,

I tried to adjust my sql statement according to your hints (see below)
and I'm getting "invalid procedure call or argument".

In your original post, the error was caused by the Set StrSql = "..".
Set is only necessary and valid for object variables. So ensure that you
don't have it in your actual code.

StrSql = "SELECT tblActData.Dept, tblActData.Amount,
tblActData.COAKey, tlkpCOA.Number, " _
        & " tlkpCOA.Account, tblActData.Subsidiary,
tblActData.FiscalYr, " _
        & " tblActData.FiscalMonth, " _
        & " #" & "DateSerial([tblActData.FiscalYr],
[tblActData.FiscalMonth],1) # " _
        & " FROM tblActData INNER JOIN tlkpCOA ON tblActData.COAKey =
tlkpCOA.COAKey " _
        & " WHERE tblActData.COAKey = " & lngCOAKey _
        & " AND tblActData.Subsidiary = '" & strSubsidiary & "'" _
        & " AND #(DateSerial([tblActData.FiscalYr],
[tblActData.FiscalMonth], 1) #" _
        & " Between # " & DateAdd("'m'", -12, dtmEndDate) _
        & " # And #" & dtmEndDate & "#"

First of all, use table alias names and reformat it, e.g.

StrSql = "SELECT D.Dept, D.Amount, D.COAKey, C.Number, " & _
          "C.Account, D.Subsidiary, D.FiscalYr, D.FiscalMonth, " & _
          "DateSerial([D.FiscalYr], [D.FiscalMonth], 1) " & _
          "FROM tblActData D " & _
          "INNER JOIN tlkpCOA C ON D.COAKey = C.COAKey " & _
          "WHERE D.COAKey = " & lngCOAKey & " " & _
          "AND D.Subsidiary = '" & strSubsidiary & "' " & _
          "AND DateSerial([D.FiscalYr], [D.FiscalMonth], 1) " &_
          "BETWEEN " & _
          Format(DateAdd("m", -12, dtmEndDate), "#mm/dd/yyyy#")& " " & _
          "AND " & Format(dtmEndDate, "#mm/dd/yyyy#")

mfG
--> stefan <--

That's it, Stefan! Thank you ! I finally understand why you'd use
the Alias. I've seen it, but never used it. Much simpler.

I had to make 2 small changes - I don't need the # signs when using
"Format" - I wish I had known that before! It's working
beautifully- at least for now. I feel like I've just programmed the
space shuttle!

Thanks again - I'm on to the next problem now.

Sara

 

[saraqpost]

hi Sara,

In your original post, the error was caused by the Set StrSql = "..".
Set is only necessary and valid for object variables. So ensure that you
don't have it in your actual code.

StrSql = "SELECT tblActData.Dept, tblActData.Amount,
tblActData.COAKey, tlkpCOA.Number, " _
        & " tlkpCOA.Account, tblActData.Subsidiary,
tblActData.FiscalYr, " _
        & " tblActData.FiscalMonth, " _
        & " #" & "DateSerial([tblActData.FiscalYr],
[tblActData.FiscalMonth],1) # " _
        & " FROM tblActData INNER JOIN tlkpCOA ON tblActData.COAKey =
tlkpCOA.COAKey " _
        & " WHERE tblActData.COAKey = " & lngCOAKey _
        & " AND tblActData.Subsidiary = '" & strSubsidiary & "'" _
        & " AND #(DateSerial([tblActData.FiscalYr],
[tblActData.FiscalMonth], 1) #" _
        & " Between # " & DateAdd("'m'", -12, dtmEndDate) _
        & " # And #" & dtmEndDate & "#"

First of all, use table alias names and reformat it, e.g.

StrSql = "SELECT D.Dept, D.Amount, D.COAKey, C.Number, " & _
          "C.Account, D.Subsidiary, D.FiscalYr, D.FiscalMonth, " & _
          "DateSerial([D.FiscalYr], [D.FiscalMonth], 1) " & _
          "FROM tblActData D " & _
          "INNER JOIN tlkpCOA C ON D.COAKey = C.COAKey " & _
          "WHERE D.COAKey = " & lngCOAKey & " " & _
          "AND D.Subsidiary = '" & strSubsidiary & "' " & _
          "AND DateSerial([D.FiscalYr], [D.FiscalMonth], 1) "& _
          "BETWEEN " & _
          Format(DateAdd("m", -12, dtmEndDate), "#mm/dd/yyyy#") & " " & _
          "AND " & Format(dtmEndDate, "#mm/dd/yyyy#")

mfG
--> stefan <--

That's it, Stefan!  Thank you !  I finally understand why you'd use
the Alias.  I've seen it, but never used it.  Much simpler.

I had to make 2 small changes - I don't need the # signs when using
"Format"  -  I wish I had known that before!  It's working
beautifully- at least for now.  I feel like I've just programmed the
space shuttle!

Thanks again - I'm on to the next problem now.

Sara- Hide quoted text -

- Show quoted text -

Well, now I am not getting the results I expect!

SELECT D.Dept, D.Amount, D.COAKey, C.Number, C.Account, D.Subsidiary,
D.FiscalYr, D.FiscalMonth, DateSerial([D.FiscalYr], [D.FiscalMonth],
1) FROM tblActData D INNER JOIN tlkpCOA C ON D.COAKey = C.COAKey WHERE
D.COAKey = 390 AND D.Subsidiary = 'Parent Company' AND DateSerial
([D.FiscalYr], [D.FiscalMonth], 1) BETWEEN 05/01/2009 AND 05/01/10
ORDER BY D.Dept, D.FiscalYr, D.FiscalMonth;

from my code:
StrSql = "SELECT D.Dept, D.Amount, D.COAKey, C.Number, " & _
" C.Account, D.Subsidiary, D.FiscalYr, D.FiscalMonth,
" & _
" DateSerial([D.FiscalYr],[D.FiscalMonth],1) " & _
" FROM tblActData D " & _
" INNER JOIN tlkpCOA C ON D.COAKey = C.COAKey " & _
" WHERE D.COAKey = " & lngCOAKey & " " & _
" AND D.Subsidiary = '" & strSubsidiary & "' " & _
" AND DateSerial([D.FiscalYr], [D.FiscalMonth], 1) " &
_
" BETWEEN " & Format(DateAdd("m", -12, dtmEndDate),
"mm/dd/yyyy") & " " & _
" AND " & Format(dtmEndDate, "mm/dd/yyyy")

is how my SQL statement resolves, yet the "recordcount" *which is why
I'm doing this!* keeps coming out as zero. Yet there are over 200
records in the test I'm running.

If I just open the form using the query - I get the 274 records. For
some reason, the code isn't producing the same results - why would
that be?

The query code is:
SELECT tblActData.Dept, tblActData.Amount, tblActData.COAKey,
tlkpCOA.Number, tlkpCOA.Account, tblActData.Subsidiary,
tblActData.FiscalYr, tblActData.FiscalMonth, DateSerial([FiscalYr],
[FiscalMonth],1) AS Expr1, DateSerial([FiscalYr],[FiscalMonth],1) AS
MyDate, DateSerial([Forms]![frmChooseAccount]![cboFiscalYear],[Forms]!
[frmChooseAccount]![cboFiscalMonth],1) AS CalcEndDate

FROM tblActData INNER JOIN tlkpCOA ON tblActData.COAKey =
tlkpCOA.COAKey

WHERE (((tblActData.COAKey)=[Forms]![frmChooseAccount]!
[cboChooseAccount]) AND
((tblActData.Subsidiary)=[Forms]![frmChooseAccount]![cboSubsidiary])
AND
((DateSerial([FiscalYr],[FiscalMonth],1)) Between DateAdd
("m",-12,DateSerial([Forms].[frmChooseAccount].[cboFiscalYear],[Forms].
[frmChooseAccount].[cboFiscalMonth],1)) And
DateSerial([Forms].[frmChooseAccount].[cboFiscalYear],[Forms].
[frmChooseAccount].[cboFiscalMonth],1)))

ORDER BY tblActData.Dept, tblActData.FiscalYr, tblActData.FiscalMonth;

There are a few extra fields I don't need - I was just using them to
debug (like My Date). End Date (which is CalcEnd Date) I get from the
form and resolve in the code. I'm not bothering with "order by" since
I'm just counting, not displaying this info.

I've recreated the entire SQL from your code, from scratch - I'm just
not clear what's missing (or wrong!) HELP, Please!!!

(And I no longer feel like a NASA programmer.....)

Sara

 

[John W. Vinson]

SELECT D.Dept, D.Amount, D.COAKey, C.Number, C.Account, D.Subsidiary,
D.FiscalYr, D.FiscalMonth, DateSerial([D.FiscalYr], [D.FiscalMonth],
1) FROM tblActData D INNER JOIN tlkpCOA C ON D.COAKey = C.COAKey WHERE
D.COAKey = 390 AND D.Subsidiary = 'Parent Company' AND DateSerial
([D.FiscalYr], [D.FiscalMonth], 1) BETWEEN 05/01/2009 AND 05/01/10
ORDER BY D.Dept, D.FiscalYr, D.FiscalMonth;

Reread the suggestion. The # delimiters are essential:

 

[saraqpost]

SELECT D.Dept, D.Amount, D.COAKey, C.Number, C.Account, D.Subsidiary,
D.FiscalYr, D.FiscalMonth, DateSerial([D.FiscalYr], [D.FiscalMonth],
1) FROM tblActData D INNER JOIN tlkpCOA C ON D.COAKey = C.COAKey WHERE
D.COAKey = 390 AND D.Subsidiary = 'Parent Company' AND DateSerial
([D.FiscalYr], [D.FiscalMonth], 1) BETWEEN 05/01/2009  AND  05/01/10
ORDER BY D.Dept, D.FiscalYr, D.FiscalMonth;

Reread the suggestion. The # delimiters are essential:

Well, that explains why I had learned that, John!

What happened, though, is that when I used the code as it is, I get a
Syntax error:
"Between 3996mm/dd/yy5 and 4033mm/dd/yy0

The only way I found to get this to compile was to remove the hash
marks - and my uncertainty with that action is the reason I posted
that I removed them.

Can you help in the formatting of that phrase that will work?

thanks -
Sara

 

[saraqpost]

SELECT D.Dept, D.Amount, D.COAKey, C.Number, C.Account, D.Subsidiary,
D.FiscalYr, D.FiscalMonth, DateSerial([D.FiscalYr], [D.FiscalMonth],
1) FROM tblActData D INNER JOIN tlkpCOA C ON D.COAKey = C.COAKey WHERE
D.COAKey = 390 AND D.Subsidiary = 'Parent Company' AND DateSerial
([D.FiscalYr], [D.FiscalMonth], 1) BETWEEN 05/01/2009  AND  05/01/10
ORDER BY D.Dept, D.FiscalYr, D.FiscalMonth;

Reread the suggestion. The # delimiters are essential:

And now I tried:
" BETWEEN #" & Format(DateAdd("m", -12, dtmEndDate), "mm/dd/yyyy" &
"#") & " " & _
" AND #" & Format(dtmEndDate, "mm/dd/yyyy") & "#"

and it seems to work - did I get it? I am pretty surprised I finally
got something to work, but I want to make sure I'm not fooling myself
here.

Thanks -
Sara

 

[John W. Vinson]

And now I tried:
" BETWEEN #" & Format(DateAdd("m", -12, dtmEndDate), "mm/dd/yyyy" &
"#") & " " & _
" AND #" & Format(dtmEndDate, "mm/dd/yyyy") & "#"

and it seems to work - did I get it? I am pretty surprised I finally
got something to work, but I want to make sure I'm not fooling myself

That's another way to get the # marks in, and (especially since it works!) I'm
fine with it. I suspect the problem had to do with the definition or datatype
of dtmEndDate.

 

[saraqpost]

Would it work to do this?

Format(dtmEndDate, "\#mm/dd/yyyy\#")

I hope so, because I suggested it earlier today in another thread.

And now I tried:
" BETWEEN #" & Format(DateAdd("m", -12, dtmEndDate), "mm/dd/yyyy" &

[quoted text clipped - 3 lines]

and it seems to work - did I get it?  I am pretty surprised I finally
got something to work, but I want to make sure I'm not fooling myself

That's another way to get the # marks in, and (especially since it works!) I'm
fine with it. I suspect the problem had to do with the definition or datatype
of dtmEndDate.

Hi Bruce -
I got a syntax error when I put this line in - just copied and pasted
it. So I don't think this works. Sorry.

I'm happy to try any other syntax you want to try...


Sara

 

[Stefan Hoffmann]

hi Bruce,

Would it work to do this?

Format(dtmEndDate, "\#mm/dd/yyyy\#")

I hope so, because I suggested it earlier today in another thread.

Oops, sure, this was a mistake of mine..

mfG
--> stefan <--

 

[John W. Vinson]

Would it work to do this?

Format(dtmEndDate, "\#mm/dd/yyyy\#")

I hope so, because I suggested it earlier today in another thread.

Certainly should.

 

[saraqpost]

It worked in the Immediate window (using the Date function).  Did you remove
the other # symbols in the string?

[quoted text clipped - 14 lines]

Hi Bruce -
I got a syntax error when I put this line in - just copied and pasted
it.  So I don't think this works.  Sorry.

I'm happy to try any other syntax you want to try...

Sara

Sorry - I must have had another error there - I tried it again now and
it worked! My my, how easy some things can be! I'm going to have to
use this one again!

 

[saraqpost]

Glad to hear you got it working.  The back slash indicates the next character
is literal.  I have seen the formatting done this way:
Format(dtmEndDate,"\#mm\/dd\/yyyy\#"), but it does not seem to be necessary
to indicate slashes as literal characters.

Format can be rather confusing.  You can use literal characters to do
something such as add a prefix to a number with leading zeros:
Format(2, "\P\R\E 0000")
returns:
PRE 0002

In some cases such as this one you may be able to get away with not
indicating literal characters via the back slash.  However, if any of the
letters in the prefix (or anywhere in the expression) are format symbols such
as y, q, m, etc. that are not immediately preceded by a back slash it will
not work.  I would put a back slash before all literal characters as a matter
of course.  The space seem to be treated as a literal character in all cases,
but there is no harm to preceding it with a back slash.

In most cases I would concatenate:

"PRE " & Format(2,"0000")

but using format to add literal characters can be helpful in some cases.

It worked in the Immediate window (using the Date function).  Did you remove
the other # symbols in the string?

[quoted text clipped - 16 lines]

Sorry - I must have had another error there - I tried it again now and
it worked!  My my, how easy some things can be!  I'm going to have to
use this one again!

Thanks for this, Bruce. I will keep this information with all my
other "code tips" - and I will use it more in this little project too,
I'm sure. We're actually looking at a commercial application that
attaches to our ERP system for this effort, but it might not happen
real soon. And this is good for me to "dust off" my buried Access
skills.

Again, thank you.
Sara

 

[florine]

Glad to hear you got it working. The back slash indicates the next
character
is literal. I have seen the formatting done this way:
Format(dtmEndDate,"\#mm\/dd\/yyyy\#"), but it does not seem to be
necessary
to indicate slashes as literal characters.

Format can be rather confusing. You can use literal characters to do
something such as add a prefix to a number with leading zeros:
Format(2, "\P\R\E 0000")
returns:
PRE 0002

In some cases such as this one you may be able to get away with not
indicating literal characters via the back slash. However, if any of the
letters in the prefix (or anywhere in the expression) are format symbols
such
as y, q, m, etc. that are not immediately preceded by a back slash it will
not work. I would put a back slash before all literal characters as a
matter
of course. The space seem to be treated as a literal character in all
cases,
but there is no harm to preceding it with a back slash.

In most cases I would concatenate:

"PRE " & Format(2,"0000")

but using format to add literal characters can be helpful in some cases.

It worked in the Immediate window (using the Date function). Did you
remove
the other # symbols in the string?

[quoted text clipped - 16 lines]
Sorry - I must have had another error there - I tried it again now and
it worked! My my, how easy some things can be! I'm going to have to
use this one again!

 

[florine]

Glad to hear you got it working. The back slash indicates the next
character
is literal. I have seen the formatting done this way:
Format(dtmEndDate,"\#mm\/dd\/yyyy\#"), but it does not seem to be
necessary
to indicate slashes as literal characters.

Format can be rather confusing. You can use literal characters to do
something such as add a prefix to a number with leading zeros:
Format(2, "\P\R\E 0000")
returns:
PRE 0002

In some cases such as this one you may be able to get away with not
indicating literal characters via the back slash. However, if any of the
letters in the prefix (or anywhere in the expression) are format symbols
such
as y, q, m, etc. that are not immediately preceded by a back slash it will
not work. I would put a back slash before all literal characters as a
matter
of course. The space seem to be treated as a literal character in all
cases,
but there is no harm to preceding it with a back slash.

In most cases I would concatenate:

"PRE " & Format(2,"0000")

but using format to add literal characters can be helpful in some cases.

It worked in the Immediate window (using the Date function). Did you
remove
the other # symbols in the string?

[quoted text clipped - 16 lines]
Sorry - I must have had another error there - I tried it again now and
it worked! My my, how easy some things can be! I'm going to have to
use this one again!