Search results

yes CLng fn works. Thanks.

all values for [Price File] are 4, 5, or 6 characters (digits). The mark in the brackets is the letter 'L' and is not in the SQLString (proprietary data that I modify so I can show in Forum - forgot to erase the L from my original when I cut and pasted to my post). That said, the query throws...

not sure what that little mark is at the end of my SQLString but it is not in the Access code line

CInt([Price File]) > 0 throws an error. I think there must be some sort of Access syntax I am missing " Invalid SQL statement; expected 'Delete', 'Insert', 'Procedure' .... Actual debug.print: Select [PIR Vendor], Vendor_Name, Material, Description, MIC, [Base UOM], [Net Price] FROM...

will try this.

Need to use CAST to return integer value of string (digits as data type string). Where clause looks like this: ... Where Cast([Price File] as int) > 0 works fine in SQL Server but not sure what syntax is in VBA . Using Paul Baldy's suggestion to set Select statement as string and do the...

thanks that worked. the one combination of quote marks, ampersand, and apostrophes I didn't use

I am struggling with the proper syntax to use in SQL statement that includes derived field. Consider: dim strValue as double dim strSQL as string dimstrSQL = "Select abc, def, ghi, (Price * strValue) AS Extended From CustInvoice in this example we are querying the table CustInvoice and...

Ah, foolish me. I assumed that compile took place as part of the save as ACCDE. Why? Because that is what Microsoft has coded into the ACCDE button: "File will be compiled into an executable only file". Thanks for setting me straight. Jeff

here is a trival database that illustrates my question: create database dbCarLot go Use dbCarLot go create table CarInventory (cinv_Id int primary key, cinv_Make varchar(15) not null, cinv_Model varchar(12) not null, cinv_AquiredDate date not null) 　 insert into CarInventory values(1, 'Ford'...

Trying to lock down an application by saving as ACCDE file. Saves OK but when you load application none of the controls work. I.e. button clicks do not launch "Open Form" or " Print Report" commands. But in ACCDB format application works flawlessly. When I tried on a different machine I get...

Thanks both. I am not explaining myself well. Everything you have said works and completes the task - I was just questioning the efficiency of the process. Your explanations are telling me that it is the way Access works and the way Access was designed. I can certainly accommodate. I will try...

A great suggestion. I will definitely try that when I hit a wall again in the future. Thanks to all that offered up help. One more word and then I will shut up: It seems a bit inelegent to have to make a build (query by example) to extract the month component out of a field that is formatted...

you are correct that is what I am familiar with. i.e. query builder or just build.

that works. I was confused by the "query by example". I was able to do the same task in a more round about way by exporting to Excel, parcing using / as delimiter and bringing a new integer field back into Access called birthmonth. your solution is quicker and less prone to introduce errors into...

not sure what query by example grid is (not sure Microsoft does either as it does not appear in their help) this has got to be the easiest thing in the world to do. I have a field with date of birth and I want to query all persons in the database who have a birthday in the month of October

that does not seem to work. this statement is going in criteria line of field "DOB" in query design view. "DOB" field is formated short date and has input mask ##/##/####. If I enter your solution Where Month(DOB) = 10 Access first converts to Where Month("DOB") = 10 and upon execution (!)...

I have a field that is short date data type and it represents "date of birth". If I want to query all records in the data base that have persons born in October, what will my statement look like in the criteria line (in query design view) for the field 'DOB'?

Paul Thanks for all the help. With your help and tutorial I was able to accomplish what I needed in Access 2010. Program is now executable and returns the correct data based upon user supplied values for variables that are loaded into the SQL statements. I was just wondering if it wouldn't...

Paul, I will look again at #1. #2 - That's a win! as soon as I used your syntax and cleaned up my type mismatch, query ran fine. Just need to study it and learn the logic behind the syntax. #3 - i will noodle around with VB this weekend and see if i can get it to run. you can configure...