Access 97 to 2010 conversion problem

[Soulspike]

I have recently converted a database built in Access 97 to Access 2010. In 2010 I am now having a problem with an control on the form. The control now states #Name? as the shown value. I have the text box Control source set as:

= DCount("[RN]","tbl_DIC","[Location] = '" & [myShop] & "'  AND [Removed_Date] > #" & DateAdd("h",-24,[myDate]) & "# and  [Removed_Date] <= #" & [myDate] & "# and [Executed] ")

myShop = Variable from vb
myDate = Variable from vb
RN = Table value
Removed_Date = Table value
Executed = Table value

Can anyone tell me why this is not working. I have been back and forth and can not find a problem with the dcount function. This method works just fine in 97.

Thank you in advance for the help.

[Rabbit]

The most likely cause is a variable name, field name, or table name that got changed in the conversion. I would check all those references.

[TheSmileyCoder]

I believe it is related to trying to use a VB variable directly in the control source. Can you say a bit more about myShop and myDate? How are they declared within the vb environment, and how and when are they set?

[Soulspike]

The variables are

Public myDate As Date
Public myShop As String

They are set on load of the form through open args, I have a parse function that allows me to send multiple variables.

[TheSmileyCoder]

Now I have never used Ac97 myself, but if memory serves I recall someone else asking something similar, in which it was determined that you can't access form variables like that(like in the Dlookup) anymore, in the more recent versions of access.

The simplest workaround is to use 2 hidden and unbound textboxes, assign values to them in the forms load event, and use their reference in your dlookup syntax.

[NeoPa]

Spot on Smiley. I would add though, that 2010 has session variables available that would probably be a more appropriate substitute.

[TheSmileyCoder]

I would say that its certainly possible to use the session variables (Tempvars), however I see no reason for those variables to become global (at least from what has been described), and would at least personally prefer the hidden control approach.

[NeoPa]

Yes. It was TempVars I meant ;-)

I still use the control approach myself sometimes, but I'm still using 2003 mainly of course. If that makes better sense to you then it sounds fine to me :-)

[Soulspike]

Thank you all for you quick response on this. Setting hidden controls worked perfectly. I will do some research into the TempVars as I am not familiar with that.

[TheSmileyCoder]

Your welcome. Neopa, Rabbit and me are pleased to help.

[zmbd]

@2.W.M.C.:
I've no problem with using the public variables in the declaration section of my forms within dlookup.
In fact, I have one form where I store an SQL that is built based on user choices from an opening form... passing the values as openarg. This is a database that was in v2003 and now in v2010 (finally just bit the bullet and saved it as ACCDB as I wanted to remake the switchboard using the new navigation control).
I'd be willing to post an example DB with a very simple form that shows this to work.


@Soulspike:
I'm glad you have a solution; however, the way you built the criteria for the dlookup function is a pet-peeve of mine. I don't blame you, I blame MS and every textbook author out there that shows programmers to build the string within the criteria parameter of the dlookup function, or within any function for that matter.

You have this huge string being built within the dlookup function... and I'll tell you now, the more complex the string - more likely it's going to choke on you when this function resolves - I found this out the hard way!

First pull that thing out of the function and assign it to a "strVAR" so that you can see the string. You use that strVAR instead within the function.

So you'll have something like:

'lead-in code omitted
strVAR= "[Location] = '" & [myShop] & _
    "'  AND [Removed_Date] > #" & _
    DateAdd("h",-24,[myDate]) & _
    "# and  [Removed_Date] <= #" & _
    [myDate] & "# AND [Executed]"
'
'place your stop or debug.print strVAR here.
'
strSOMETHING= DCount("[RN]","tbl_DIC", strVAR) 
 

Now you can either debug print or place a stop command in the code to take a look at what you are actually getting back

I find that line 6 above to be interesting:
"# AND [Executed]"
That is not going to return the value from the control or record field [Executed], so your are getting that exact string appended after your date; thus you might have something like this for the resolved string:

"[Location] = 'exampleName' AND [Removed_Date] >#02/07/2013# AND [Removed_Date] <= #02/08/2013# AND [Executed]"

Is that what you wanted? I suspect that is where your #Name error is comming from.

[Soulspike]

I will try out using the strVar method you are showing above, it does sound cleaner. This forum is a great place for me to learn better ways to do it. My biggest problem being self taught is keeping things clean and organized in my code. So I appreciate the ideas to help me improve that.

As far as the string

"[Location] = 'exampleName' AND [Removed_Date] >#02/07/2013# AND [Removed_Date] <= #02/08/2013# AND [Executed]"

That looks right to me as [Executed] is a True/False value and I only want the trues so access is only counting those as a part of that function.

[zmbd]

AND [Executed]
I was wondering if that was a Boolean datatype. Personally, I would have been explicit with the data check: AND [Executed]= True; however, I come from the old school where a lot things had to be explicitly defined and I'm always suspect that the engine wont look at things correctly.

[NeoPa]

Z:
I would have been explicit with the data check: AND [Executed]= True

I'm afraid that would not be recommended Z.

Apart from being technically incorrect ((BooleanVar = True) =/=> (BooleanVar)) it also indicates a lack of the appreciation that the result required is exactly what a Boolean value is.

Technically, any Boolean result is treated as being on the True path as long as it is not zero. Thus, an arithmetic equivalence can only be deemed correct if it is in the form (BooleanVar <> False). That said, my bigger gripe with that approach is that it indicates a lack of understanding of what Booleans are. It really makes no sense to convert a Boolean value into Boolean by comparing it with another Boolean. Very similar to using tautology in the language.

[zmbd]

Simply forgot that the [Executed] was actually returning the value from the table and not refering just to the field name. I've been up since 05h00 Thursday... perhaps a nap is in order.