[AccessD] Data Type Mismatch in criteria expression

[Bill Benson]

I think space or some irregularity like a '#' prefix, or when using a field
name that is also a reserved word I think it is generally preferred.

SELECT Table1.ID, Table1.[#Agent], Table1.[Name]
FROM Table1;


On Tue, Dec 15, 2015 at 7:27 PM, Steve Schapel <
steve at datamanagementsolutions.biz> wrote:

> Hi Don
>
> Just a minor point in the context of the discussion, but... I think the
> brackets would only be necessary if there was a space in the name.
>
> Regards
> Steve
>
> -----Original Message----- From: McGillivray, Don
> Sent: Wednesday, December 16, 2015 11:28 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Data Type Mismatch in criteria expression
>
>
> Oops.  Meant to point out also that the column name "agentid" needs the
> square brackets around it.
>
> -----Original Message-----
> From: McGillivray, Don
> Sent: Tuesday, December 15, 2015 2:27 PM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] Data Type Mismatch in criteria expression
>
> Try:
>
> DCount("[agentid]", "agent", "[agentid] = '" & strAgentID & "'")
>
> The criterion, being a string, must be surrounded by ' .
>
>
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Bob Heygood
> Sent: Tuesday, December 15, 2015 2:13 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Data Type Mismatch in criteria expression
>
> If you have a moment, can you look at this snippet ?
>
>
> Don't judge me, I inherited the table and field names. And I figured it
> was too much to change all the rookie naming conventions....
>
> I just can't see the problem.
>
> "agentid" is a field name in the table "agent". It is a primary key and a
> STRING data type.
> intAgentCount is a integer type vba variable.
> strAgentID is a string type vba variable.
>
>    intAgentCount = DCount("agentid", "agent", "[agentid] = " & strAgentID)
>
> My objective is to determine if a record is already in the table for a
> given agentid, and if not, to insert one.
>
> TIA
>
> Bob Heygood
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>