Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 6 votes, 5.00 average. Display Modes
Old 09-04-2008, 05:54 AM   #1
themanof83
Newly Registered User
 
Join Date: May 2008
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
themanof83 is on a distinguished road
Unhappy On the Edge of Insanity......

Please can someone spot the errors of my ways, this is driving me nuts...

I am trying to see if a record already exists matching the criteria used in the DCount function. I have used this successfully in other areas of my project but it doesn't seem to like this one!!!
I am getting a Data Type mismatch in Criteria expression - error.

Folder Name (text) and Programme ID (number) are fields in table Folders. I am seeing whether a record exists in the table that matches the values entered on a form (Me.Main_Folder and Me.ProgID). As follows:

stLinkCriteria = "[Folder Name]=" & "'" & Me.Main_Folder & "' AND [Programme ID]='" & Me.ProgID & "'"

Dup = DCount("[Folder Name]", "Folders", stLinkCriteria)

I guarentee its something stupid I've done but I have no hair left....

Thanks in advance....!

themanof83 is offline   Reply With Quote
Old 09-04-2008, 05:59 AM   #2
KenHigg
Registered User
 
Join Date: Jun 2004
Posts: 13,309
Thanks: 8
Thanked 155 Times in 129 Posts
KenHigg has a spectacular aura about KenHigg has a spectacular aura about
Re: On the Edge of Insanity......

Is [Programme ID] a numeric data type?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

ken

“You don’t learn to walk by following rules. You learn by doing, and by falling over.”
KenHigg is offline   Reply With Quote
Old 09-04-2008, 11:14 PM   #3
CyberLynx
Stuck On My Opinions
 
Join Date: Jan 2008
Posts: 585
Thanks: 0
Thanked 5 Times in 5 Posts
CyberLynx will become famous soon enough
Re: On the Edge of Insanity......

stLinkCriteria = "[Folder Name]='" & Me.Main_Folder & "' AND [Programme ID]=" & Me.ProgID

Dup = DCount("[Folder Name]", "Folders", stLinkCriteria)

.

__________________
Self taught in all Environments.....and it shows
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
CyberLynx is offline   Reply With Quote
Old 09-05-2008, 01:56 AM   #4
themanof83
Newly Registered User
 
Join Date: May 2008
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
themanof83 is on a distinguished road
Re: On the Edge of Insanity......

Thanks CyberLynx, now works fine...!

Could you possibly explain the logic behind the quotation marks etc. as it is a common problem I have?

Thanx again.
themanof83 is offline   Reply With Quote
Old 09-05-2008, 02:15 AM   #5
KenHigg
Registered User
 
Join Date: Jun 2004
Posts: 13,309
Thanks: 8
Thanked 155 Times in 129 Posts
KenHigg has a spectacular aura about KenHigg has a spectacular aura about
Re: On the Edge of Insanity......

Anything that is a text datatype needs quotes around it whereas numeric data types do not. So if Me.Main_Folder equals 'MyFolderName' then the [Folder Name] parts looks something like:

[Folder Name] = 'MyFolderName'

What you had there should have actually worked. It was the numeric [Programme ID] part that you put quotes around which caused the error and it didn't need the quotes. So that part needed to look something like:

[Programme ID] = 1234

In your code that errored that part evaluated to something like:

[Programme ID] = '1234'

Hope that makes sense -
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

ken

“You don’t learn to walk by following rules. You learn by doing, and by falling over.”
KenHigg is offline   Reply With Quote
Old 09-05-2008, 10:43 AM   #6
CyberLynx
Stuck On My Opinions
 
Join Date: Jan 2008
Posts: 585
Thanks: 0
Thanked 5 Times in 5 Posts
CyberLynx will become famous soon enough
Re: On the Edge of Insanity......

And...if you are going to be applying a Date or Time to a query string (SQL) or Filter etc., it will need to be enclosed between the Hash Marks ( # ). For Example:

[Start Date] = #" & Me.MyDateTextBox & "#"

which evaluates to

[Start Date] = #01/01/2008#

.

__________________
Self taught in all Environments.....and it shows
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
CyberLynx is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Text Too Close To Edge DanG Excel 6 07-06-2007 08:41 AM
Table Relationship Insanity don_b Tables 7 05-17-2007 09:15 AM
Color of a border edge AshikHusein Excel 2 04-23-2007 07:31 AM
Access to visual basic using Diamond edge Happy YN General 3 11-02-2003 01:56 PM
Table disapperaed off edge of relationship screen matttt Tables 2 01-16-2003 10:16 AM




All times are GMT -8. The time now is 07:54 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World