Queries :: Make Boolean (Yes / No) Field With SELECT INTO Sql Statement

Nov 28, 2013

I've been using a SELECT INTO statement to import data from a linked text file into a temporary table in Access. Something along the lines of :

SELECT [tblLink].[fld1] AS Field1,
[tblLink].[fld2] AS Field2,
[tblLink].[fld3] AS Field3
INTO [tblTemp]
FROM [tblLink]

(There's an INNER JOIN in there and some Nz / CLng functions but just want to keep it simple...)

Now - I've just realised I also need to create a couple of extra 'dummy' fields in my temporary table (for later on in the show) and I need them to be Yes/No format (will set them to False at first, then run some separate queries later to update them)

I tried this :

SELECT [tblLink].[fld1] AS Field1,
[tblLink].[fld2] AS Field2,
[tblLink].[fld3] AS Field3,
False AS Field4,
False AS Field5
INTO [tblTemp]
FROM [tblLink]

But this sets Field4 and Field5 as Number fields, with each record given a value of 0. What syntax is required in the SQL to make these fields Yes/No rather than Number?

View Replies


ADVERTISEMENT

Queries :: Select Query Return (text) Instead Of (Boolean Value)

Mar 26, 2015

how to do to return a text for each row (as field value) when a table field contains "1" as value ?

for example i have a table named "products" with a field/column called "promotion". Sometime a product is promotional, so in this case, the "promo" column holds "1" as value.

during a select on products table, how can i do to return "in promotion" (e.g.) if the column "promo" holds "1" for a product ?

View 1 Replies View Related

Queries :: Make A Nested If Then Statement In A Query Field

Apr 27, 2014

I'm trying to make a nested if then statement in a query field, and I can't figure out why I can't get my formula to work:

Volume: IIf([MethodCode]="K",[total]*12.54*0.026873,IIf([MethodCode]="S",([length]*[width]*[depth])/2,IIf([MethodCode]="M" And [Location]="SH",[total]*5.08*0.026873,IIf([MethodCode]="M" And [Location]="C",[total]*18.58*0.026873," "))))

I keep getting the "data type mismatch in criteria expression" error. If I separate out all the individual if then statements individually, they work. But if I connect them all as a nested if then it doesn't work.

View 6 Replies View Related

Queries :: Setting Field Width To Zero In Select Statement

Jun 11, 2015

I am building a form that uses list box selections on the form to make a temporary query and open the results in Excel.

It mostly works in just trying to make it more functional.

Currently the listbox that contains the first and last names of the personnel also has a unique shorttext 'number' as a primary key for the table (bad choice in my opinion but I didn't design the database I just have to work with it).

The short text primary key is hidden by an option given in the listbox wizard that let me set that field width to zero but still search on it. This is how I currently build the query's where clause when it involves names. I search the primary key.

I would like to update the names list box based upon which cities and locations are selected (both are multi-select).

Do I need to change how I make the "where" clause to use first and last names or an I use the same query and just hide the USER_ID

The query looks like this

Code:

SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID
FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO
ORDER BY t_asset_personnel.LAST_NAME;

And the list box wizard hides USER_PHY_ID

By setting the width to zero

If I make my own query in the City_After_Update()

Can I do something like this

Code:

SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID
.fieldWidth(0)FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO
WHERE [forms]![myform]![citylist].[itemsSelected]'obviously more code is needed here this is just for conceptual illustration
ORDER BY t_asset_personnel.LAST_NAME;

View 7 Replies View Related

Queries :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?

View 2 Replies View Related

Queries :: How To Make Whole Statement To Work

Jun 14, 2013

I am trying to get an IIf statement to work, but I am not quite sure how I can lay it out properly so that it works.So this is what I need

(IIF(([PAON_TEXT]= "Abbey Parade" AND [SAON_TEXT] AND [SAON_START_NUM]), (THATS THE CONDITION)
[SAON_START_NUM] & " ", (THATS THE TRUE PART)
(IIf(Not IsNull([SAON_TEXT]),[SAON_TEXT] & " ","") & (IIf(Not IsNull([SAON_START_NUM]),[SAON_START_NUM],"") & IIf(Not IsNull([SAON_START_SUFFIX]),[SAON_START_SUFFIX] & " ") & IIf(Not IsNull([SAON_END_NUM])," - " & [SAON_END_NUM] & " ","") & IIf(Not IsNull([SAON_END_SUFFIX]),[SAON_END_SUFFIX] & " ","") & IIF(([PAON_TEXT]= "Abbey Parade" AND [SAON_TEXT] AND [SAON_START_NUM])," ","") & iif(NOT IsNull([PAON_START_NUM])," " & [PAON_TEXT],"")
(ALL OF THIS THE FALSE PART)

I know the syntax for the FALSE PART is wrong how can I make the whole statement to work?

View 5 Replies View Related

Queries :: Select Statement To Max Value

Jul 12, 2013

I'm trying to do a select statement and put it in a variable which i can then output to a text box. How do I get the value into a variable? I can't seem to get my syntax right

This is what I currently have

Maxvalue = "SELECT MAX[Record Num]FROM Joblog"

View 2 Replies View Related

How To Format A Field In The Select Statement?

Sep 1, 2005

Hi!

I have a table called myTable and it contains two fields Year and Month. Both are type of integer.

Now in my select statement, I like to combine these two fields in to one and name it as period and it will have the format, for example 200501, 200502, ..., 200512. How can I do that in Access query statement?

I know how to do this in SQL Server 2000. That is:

Select Convert(varchar(4), [Year]) + right('00' + cast([Month] as varchar(2)), 2) as Period from myTable

How can I do that in Access Query statement?

Please advise.

Aijun.

View 4 Replies View Related

Queries :: Syntax Error Using IF In SELECT Statement

Jun 15, 2015

I'm trying to replace a null value with $0.00 for the second field in a query. My first try at the SELECT stmt did not contain any solution for a NULL value. The result was that it skipped the record. I need it to show 0.00 because the field is used in another calculated field.

My SQL:

SELECT tblRecovery.CustID, IF((Sum([tblRecovery.RecAmt]) IS NULL, 0.00, (Sum([tblRecovery.RecAmt]))) AS SumOfRecAmt
FROM tblRecovery
GROUP BY tblRecovery.CustID;

This returns : Syntax error (missing operator) in query expression 'IF(( etc.

After clicking "OK", access highlights AS in the statement. I'm not sure how to deal with the NULL value or fix the error?

View 6 Replies View Related

Modules & VBA :: Alter Table Statement To Add Field And Make It Unique

May 18, 2015

I am using the following to add a column to an existing table. How do I make this column indexed with no duplicates?

db.Execute "ALTER TABLE [BrandTBL] ADD COLUMN UPCGroupName TEXT;"

View 1 Replies View Related

Queries :: Basic Delete Insert Select Statement

Nov 8, 2013

what is wrong with the following statements. I'm new to access SQl. This would work in ms sql.

Code:
DELETE from Table1
INSERT INTO Table1(email, productid, datecreated, datesend) values ('adf', 5, '10/10/2012','10/10/2012')
Select * from Table1

View 1 Replies View Related

Queries :: Select Statement To Return Specific Data From Another Column?

Nov 3, 2014

I was just wondering if this is a possibility to do in one query or if it has to be run from a number of different queries.

I am currently developing a database from scratch for work (with very little Access experience).

The current query I am trying to run, if linked to a number of tables with different information.

What I am trying to do primarily is link stock to a specific "Host Name", "Serial Number" and "Part Description".

In the "Host Name" there is for example - A1-TX10-10001, B1-TX2-10004, C1-TX-10004 - The latter part of the name is a unique identifier number. The first part is the compartment in which the "stock" sits. So you may have all three components (A1-TX1, B1-TX2, C1-TX3) linked to the same unique identifier (10001 for example)

The serial numbers naturally are different for every single one and of course the srial numbers are linked to the "Part Description" - which will read something like....."C1-TX3 Transmitter", "B1-TX2 Combiner" etc.....

When I run the query like this the Host Name (which is also linked to the unique identifier on its own (10001) it returns everything under "A1-TX1-10001"

What I would ideally like to do is write a statement so that if the "Part Description" contains "A1-TX1" it will only return rows that contain "A1-TX1" in the Host Name and the same for "B1-TX2" and "C1-TX3" in the same query.

If "Host Name" contains "A1-TX1" to return "Part Description" to contain "A1-TX1"

View 10 Replies View Related

Queries :: SELECT CASE Statement - Populate Each Unit With Assigned Category?

Apr 9, 2013

I have a simple SELECT CASE query. I'm not sure how the syntax goes and I want to learn about it.

In the attached file, if you click on "cohort table", you will see the categorization for each unit under "field1".

how will I be able to use a SELECT CASE statement in a query to populate each unit with assigned category?

View 1 Replies View Related

Make User Select From A Drop Down List If A Number In Another Field Is Entered?

Feb 13, 2006

Hi all, i have a 2 fields in a subform named "HRS_ABSENT" & "ABSENCE_REASON" i'm trying to create some code that will display a message if the user inputs any number into the "HRS_ABSENT" field & leaves the "ABSENCE_REASON" field empty. I want to force the user to select a ABSENCE RESON (these are 3 letter codes) from the drop down list, if they enter a number in the HRS ABSENT field. Ive tried the below code but it doesnt do anything :-(

Anybody please help me out?
-------------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If HRS_ABSENT = >0 & ABSENCE_REASON = FALSE Then
MsgBox "Please select an Absence reason"
Cancel = True
End If

View 2 Replies View Related

Queries :: Count Boolean By Row?

Feb 11, 2015

I have a table with seven Boolean fields: I need to count all the "trues" and return the row if the sum of the trues is greater than 3.how to do this.

View 2 Replies View Related

Queries :: Convert Boolean Fields To Integers / Blank For Null Values

May 22, 2015

I have a query which returns, among other things, a number of boolean fields. In some cases, there will be a genuine True or False value in each of these fields; in others, it can and should be Null (e.g. as a result of a 'failed' LEFT JOIN of some description, where there is no associated record in the joined table which fulfills the criteria)

So something like this :

Code:
SELECT [tblTable2].[fldBooleanField]....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]

However, I will be writing the result of the query to a text file and here's the problem. I want to show a numeric value for a genuine True / False (i.e. -1 and 0 respectively using the standard boolean conversions in Access) and a blank for any Null values.

So I tried this :

Code:
SELECT CInt([tblTable2].[fldBooleanField]) AS fldBooleanField....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]

However, currently when I look at the exported recordset in Notepad, I am getting 0's for both False and Null values (and -1 for True)

How I can adapt my query to keep Nulls...null? And convert the genuinely present boolean values to integer form?

Only thing I can think of is to use (untested) :

Code:
IIf([tblTable2].[fldBooleanField] Is Null, Null, CInt([tblTable2].[fldBooleanField]))

But there's a number of boolean fields in there, all requiring the same treatment.

View 14 Replies View Related

Setting Some Text As Visible Or Invisible In A Tabular Form Wrt Boolean Field

May 3, 2006

Hi there,

I know there have been a good number of questions about visibility in forms already but I couldn't find a solution to my problem (or maybe I just didn't get it).

Basically, I have a tabular form (more than one record displayed at once) and one the field is of the Yes/No type. For each record, I'd like to have a text box that displays 'pending', set as visible if the field value is 'Yes' and set as invisible if the field value is 'No'.
The table is as follows:
id : auto-number
Flag: Yes/No [Yes]

If I use the following code on the Flag button:
Private Sub Flag_AfterUpdate()
Me.pending2.Visible = Me.Flag.Value
End Sub

all the 'pending' text boxes appear and disappear together (instead of just the relevant one).
I thought of using another text box, with the same data source ('Flag') but which would set itself to visible or invisible wrt to its own value but I couldn't find a way to do it.

Any suggestions ?
Thanks in advance !
and many thanks already for the forum and the contributions - it's been extremely helpful, esp. for a beginner.

View 2 Replies View Related

Queries :: IIF Statement Utilizing Yes / No Field

May 26, 2013

I am working on a hired plant database and want to limit the combo box on the hired form to only list items not on hire.I thought I could do this using the IssueDate and ReturnDate fields within a query with an IIf statement forcing the Yes/No field to choose Yes when there is a date in the IssueDate field and No when the ReturnDate field has a date entered.

View 14 Replies View Related

Queries :: If Statement As Selecting Field

Aug 27, 2013

I am having some trouble with an iif statement as selecting the field..I have two columns, one called [sdCounterpartyprice] and one called [bid]I want to select the [bid] if it's not equal to Null or 0, if it is equal to that then pick [sdCounterpartyprice]this doesn't work

Code:
CptyPrice: IIf ([bid] is null or = 0; [sdCounterpartyprice]; [bid])

View 3 Replies View Related

Queries :: How To Make Total Columns For ONE Field

Jul 2, 2015

I am trying to create a query that ONLY shows me the total count of each combo box choice for one field. It sounds very easy and I'm sure it is but everything I try tells me I am using the wrong syntax, or requires me to have rows when I only want column totals.

I have a combo box with a dropdown of 10 common places that people travel to. I want the report to ONLY show the total number of people who chose place 1, place 2, place 3... etc. But I always have to do according to date or time or something. I want it to look like this

Place 1 Place 2 Place 3 Place 4
13............36.........3........77

So essentially there would only be one row. But access will not let me. Is there something I'm doing wrong? Or is this not possible?

View 8 Replies View Related

Queries :: SQL Where Statement Linked To Fields When Field Is Empty

May 23, 2013

I'm having trouble with using a where statement linked to fields when the field is empty. I need a way to say if field is null then 'do nothing'/'select all' else use the text from the box.

I have a form (ServicesRCSSearch) which has 3 combo boxes (Location1, Location2 and Location3). These fields are linked to a query. The button on the form generates the query.

My SQL for the query is currently:

Select Services.Key, Services.Location, Services_1.Location, Services_2.Location
From Services, Services_1, Services_2 (copies of the same table all left joined)

Where
((IIf(forms!ServicesRCSSearch!Location1 Is Null,"",services.Location=forms!ServicesRCSSearch! Location1))<>False)

And ((Services_1.Location)=IIf(forms!ServicesRCSSearch !Location2 Is Null,forms!ServicesRCSSearch!Location1,forms!Servi cesRCSSearch!Location2))

And ((Services_2.Location)=IIf(forms!ServicesRCSSearch !Location3 Is Null,forms!ServicesRCSSearch!Location1,forms!Servi cesRCSSearch!Location3))

This works in that it uses the fields to filter the query but when Location1 is empty there are no results as you can see from the code.

View 6 Replies View Related

Queries :: How To Add Populated Text Field At Make Table Run

Nov 3, 2014

I can add to my make table query UDate: Date() that will add a populated field to the output table that has today's date in it.

I can also add something similar like this for example V_Num: [V_Number] and when ran, will prompt with a dialogue box to add some data, it will then create the table with that new field name and populated with the text from the dialogue box.

But how can I automate this to skip the dialogue box and just add it to the expression?

View 3 Replies View Related

Queries :: Make A Query Use Another Field For Parameter Info

Apr 10, 2013

I have a query with a parameter query but what I need is the information to be passed onto another parameter query automatically.

I have a Field called ITEMID and ITEMIDFK

the criteria for ITEMID is [ENTER Item ID Number]

once entered I need ITEMIDFK to use the same information inserted into the first parameter is this possible?

View 3 Replies View Related

Queries :: Append Query - INSERT INTO Statement Contain Unknown Field

Sep 19, 2013

I have two tables each with an ID field (autonumber/PK/No Dup etc).

I want to append two fields from one table to the other table. I have set up an Append Query to do this but it won't work - I get the following error - "The INSERT INTO statement contains the following unknown field: 'FiID'...."

View 2 Replies View Related

Queries :: Use Of String Expression As Field Definition And ARGUMENT In IIF Statement

Apr 25, 2014

I am developing a calender to display HOTEL room occupancy (past,present) and combine with future "outlook" dates and 1/0 values from active registrations that go beyond the present date.

I am working with MS ACCESS 2007. My problem is ONE SPECIFIC QUERY AND IIF STATEMENT. I want to concatenate some text (using &) along with numbers converted to text (using the CSTr function). I am using iif function and I want to use the full text string as a variable argument to be executed in the iif function. The result of the &concatenate is a text field like [p1] or [p2] or [p#] with numbers 1-31.

But, I do not want the final result as the argument. I want the query and iif to use the string expression written into the argument as the variable argument that can be calculated based on OTHER numbers that change everyday in the daily run of the calender.

The field in the statement [calc number] is a date conversion factor that changes everyday.

I want the iif statement to execute using the string as a variable argument. I am writing only within the QUERY to define the query object. I am not writing into any SQL module or code. My field definition and iif statement is below (calc number changes everyday)

CalDate18: iif(18 greater date();"[p" & [calc number] & "]";0

I do not have greater-than key on this international keyboard !

When I use this in an update query, I get format conversion error. When I use the same definition in MAKE-TABLE query, it gives the resolved value "[p1]" for the value of [CalDate18] ......... that is not what I want.

I want the string to be taken literally and executed. Seems there may be a special character to precede the argument or WRAP the argument such as done in Excel. Example # "[p" & [calc number] & "]" #

View 7 Replies View Related

How Can I Make This A IIF Statement

Nov 2, 2006

Hi again everyone,

I'm stumped again.

I have two tables that my query is looking at.

1. tblProgramActByDay
And the other is:
2. tblProgramByProductByDay

I’m trying to tell the query to
A: Look in the Program ID Field
B: if the Program ID Field = 1667100
C: Go to the table tblProgramByProductByDay

---- In that table look for the criteria---

The Field Program ID must = 0
The Field Product ID must = 38648800
The Field Locale must = en_US
The Field Report_Date must = The Current Month

If it does

Total the amount in the Sales Field from the table tblProgramByProductByDay

If not

Take the total sales for the current month from the table tblProgramActByDay

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved