Form and queries not working after conversion

Deborahp
Lounger
Posts: 35
Joined: 21 Apr 2010, 19:19

Form and queries not working after conversion

Post by Deborahp »

I am having similar issues.
My forms capture a date from a calendar and then passes that date to another form that shows only the information for that date.
It works in 97 and 2000.
When I covert to 2003, I have a blank screen.
I am not sure it it access, I think the issue is something in my version of windows.
I have a query that separates the dates...I use Left, Mid and Right to extract the dates.
That query works in windows 2000 but does not on my XP or VIsta machines.

How can a function mmonth: (IIf(Left([dateoffered],1)=0,Mid([dateoffered],2,1),Left([dateoffered],2))) work in one and not in the other???

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Form and queries not working after conversion

Post by Wendell »

Did you get a chance to check the references on hour home system as I suggested in the Windows Secrets Lounge?
Wendell
You can't see the view if you don't climb the mountain!

Deborahp
Lounger
Posts: 35
Joined: 21 Apr 2010, 19:19

Re: Form and queries not working after conversion

Post by Deborahp »

Yes, everything is the same.

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Form and queries not working after conversion

Post by Wendell »

Does that mean there were no references marked as missing? And can you run the query that contains that expression as a standalone query? I take it the date is a text string and not a Date field like Access would use to store a date. What kind of calendar is your form using? If it is the Microsoft calendar control that came with some versions of Access, you may or may not have a reference to it.
Wendell
You can't see the view if you don't climb the mountain!

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Form and queries not working after conversion

Post by HansV »

Deborahp wrote:Yes, everything is the same.
Hi Deborah,

Welcome to Eileen's Lounge!

Your problem does sound like a problem with references. Could you make a screenshot of the Tools | References... dialog (in the Visual Basic Editor!) on the "problem" PC, save it as a .png or .gif file and attach it to a reply here?
Best wishes,
Hans

Deborahp
Lounger
Posts: 35
Joined: 21 Apr 2010, 19:19

Re: Form and queries not working after conversion

Post by Deborahp »

i created the calendar from VBA.
When a user clicks on a date, it stores the date in 3 text boxes. Year, month and date.
That is used to link the main form to the subform..
In the query, I use several Mid, LEFT and RIGHT funtions to extract the parts of the date and then I use the forms!frmcalendar!month as criteria to pull out the corresponding classes on that date.
What is stumping me is that it works on my office computer but not my home pc.
We are both running XP and Access 2003 sp3.

Attached is a screen shot of my prefences.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Form and queries not working after conversion

Post by HansV »

Did you take this screenshot on your home PC? If so, references are indeed not the problem.

Do you perhaps have a different system date setting on your home PC?
Best wishes,
Hans

Deborahp
Lounger
Posts: 35
Joined: 21 Apr 2010, 19:19

Re: Form and queries not working after conversion

Post by Deborahp »

No. Everything is the same.That is what is throwing me off.
How can mmonth: (IIf(Left([dateoffered],1)=0,Mid([dateoffered],2,1),Left([dateoffered],2))) function not produce the same outcome? What could be wrong??

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Form and queries not working after conversion

Post by HansV »

It's very strange that this doesn't work. As an alternative, you might try

mmonth: Month([dateoffered])
Best wishes,
Hans

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Form and queries not working after conversion

Post by Wendell »

I think the issue here is that her date is not a date as far as Access and VBA are concerned, but a text string. Deborah, I think we need to know more about the calendar form that you built with VBA. I have seen some forms that behave like a calendar that do return dates that way, but that was a long time ago. Can you extract just the VBA and the form that provides the calendar and upload it so we can take a look at it?
Wendell
You can't see the view if you don't climb the mountain!

User avatar
Charlotte
Her Majesty
Posts: 499
Joined: 19 Jan 2010, 07:13

Re: Form and queries not working after conversion

Post by Charlotte »

Deborahp wrote:I am having similar issues.
My forms capture a date from a calendar and then passes that date to another form that shows only the information for that date.
It works in 97 and 2000.
When I covert to 2003, I have a blank screen.
I am not sure it it access, I think the issue is something in my version of windows.
I have a query that separates the dates...I use Left, Mid and Right to extract the dates.
That query works in windows 2000 but does not on my XP or VIsta machines.

How can a function mmonth: (IIf(Left([dateoffered],1)=0,Mid([dateoffered],2,1),Left([dateoffered],2))) work in one and not in the other???
If dateoffered is a date or a string that can be converted to a date, you can retrieve the month simply by using the month function on the date. You seem to be putting an awful lot of effort into retrieving 1 or two digits when you shouldn't have to retrieve any.
Charlotte

Deborahp
Lounger
Posts: 35
Joined: 21 Apr 2010, 19:19

Re: Form and queries not working after conversion

Post by Deborahp »

I agree Charlotte. I am going to try the month feature suggested by HANS tonight.
There is no real indepth formal training in access....you learn as you go and you try things over and over. It worked here...perfectly the original way I had it set up. I made the change with the month, day and year and it stills works.
Can not wait until I get home to try it!