ACCESS FEED: Re: [MS_AccessPros] help with coded select

Minggu, 01 Juli 2012

Re: [MS_AccessPros] help with coded select

you're welcome, Jackie ;) happy to help

Warm Regards,
Crystal

*
(: have an awesome day :)
*

________________________________
From: Jackie Diekmann

Crystal,
Thanks for you help.
Jackie

--- On Sun, 7/1/12, Crystal <strive4peace> wrote:

Hi Jackie,

first, I see this:

CStr(Right(Year(Date), 2))

you have to convert to a string BEFORE taking characters from the right ...

Right(CStr(Year(Date)), 2)

if you want a string with the 2-digit year, you can also do this:

Format(Date,"yy")

~~~

where is curyr DIMensioned?

Do you have OPTION EXPLICIT at the top of your module?

~~~~~ also be sure to use Option Explicit at the top of each module so variables that are not declared or are misspelled will be picked up

Option Explicit ' require variable declaration

Did you compile the code?

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should always compile and save before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!) -- then Save

Warm Regards,
Crystal

Access Basics by Crystal (Bill Mosca's site)
http://thatlldoit.com
Free 100-page book that covers essentials in Access

*
(: have an awesome day :)
*

________________________________
From: jdiek_2000 <jdiek_2000@yahoo.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Sunday, July 1, 2012 1:55 PM
Subject: [MS_AccessPros] help with coded select

Hi,
I have the following code. I keep getting Type Mismatch. I have tried numerous solutions but can't get it to work. Suggests???

Thanks,
Jackie

Dim dbs As Database
Dim rs As Recordset

Dim selpart As String
Dim selfrom As String
Dim selhave As String
Dim ssql As String

Set dbs = DBEngine.Workspaces(0).Databases(0)

curyr = CStr(Right(Year(Date), 2))

selpart = "SELECT Count(Left([companies]![coid],2)) AS totyr, Left([companies]![coid],2) AS yr"
'selpart = "select coid AS yr"
selfrom = " FROM companies GROUP BY Left([companies]![coid],2)"
selhave = " having (((Left([companies]![coid],2))='" & curyr & "'));"

ssql = selpart & selfrom & selhave

set rs = dbs.OpenRecordset(ssql)

[Non-text portions of this message have been removed]