Queries :: Value In Query Returning As String And Not A Number

Apr 3, 2014

I have a query which runs fine, however one of the outputs is a calculated field and i use the Format(someValue,"Fixed") method of specifying the format for the % Change .Here is the query

Code:

SELECT tblTempBearsWeek3.SymbolCode AS [Short Ticker], tblTempBullsWeek3.SymbolCode AS [Long Ticker], tblTempBearsWeek3.[Week 3 Date], tblTempBearsWeek3.Short, tblTempBullsWeek3.Long, tblTempBearsWeek3.[Short Week 3 Close], tblTempBullsWeek3.[Long Week 3 Close],

[code]...

The problem is that the % Change calculated field for some reason comes out as a String. i know this because when I try and sort on that column, it sorts it as if it is text and not a number value i.e. it doesn't put it in Ascending order.

View Replies


ADVERTISEMENT

Queries :: Returning Set Of Numbers From Custom Formatted String - Incorrect Use Of Join?

Mar 20, 2015

I suspect my design is flawed

Code:
SELECT tblData2.Prefix, tblData2.LineNum, tblData2.Year, tblComments.comment, tblComments.Address
FROM tblData2 LEFT JOIN tblComments ON tblData2.LineNum = (NumbersOnly([tblComments].[Address])
WHERE (((tblData2.MyYear)<1990))
ORDER BY tblData2.LineNum;

The NumbersOnly function returns a set of numbers from a custom formatted string. The above fails on the join (I think) but maybe there's another way of doing it?

View 4 Replies View Related

Modules & VBA :: Select Query Returning String Instead Of Value

May 2, 2014

Why is strCreditCheck returning the actual query string and not the result? Of course, this is not working.

Code:
Private Sub b_PrintWorkOrder_Click()
Me.Requery
Dim strCreditCheck As String
Dim strCompany_id As Integer
Dim stlinkCriteria As String

[code]....

View 6 Replies View Related

Queries :: Convert String To Number

Dec 17, 2013

how I can convert the first column to show the number? I used the expression below, but it does not convert anything with a letter or a 0

Expr1: Val([SPPC TEST]![JULY_ACCRUAL_ADJ_X])

View 7 Replies View Related

Queries :: Convert 19 Digit Number To Hex String

Mar 4, 2015

I need to run a query that converts a 19 digit number stored in a Short Text field to a hex string

Example of the string is "3310854670615838865" the result should look like "2DF284C801000091"

View 1 Replies View Related

Queries :: Convert String To Positive / Negative Number

Feb 24, 2015

I have an expression that converts a string to positive/negative number. It seems to be working fine, however, it doesn't convert "00000000001}" to -10. It shows as 10.

This is the formula that I am using in my query:

PJUNAccrual: IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "J" And "S",-1,1)*(Val([PJ_ACCRUAL_ADJ_X])/IIf(IsNumeric([PJ_ACCRUAL_ADJ_X]),100,10)+(IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "A" And "I",(Asc(Right([PJ_ACCRUAL_ADJ_X],1))-64)/100,0))+(IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "J" And "S",(Asc(Right([PJ_ACCRUAL_ADJ_X],1))-73)/100,0)))*100

View 1 Replies View Related

Queries :: Max Query Returning More Info

Jun 16, 2015

I have two tables:

tblSITE and tblUSID (joined by SITEID)

and I am trying to create a query which lists each site and the USID with the latest date in its ConstructionDate field (USID and ConstructionDate are both in tblUSID). If I omit the USID from the query and run a totals with MAX on the ConstructionDate field, I return what I would like to, but I want to add the PK (USID) of the record with the latest ConstructionDate for another query, and every way I attempt it the query returns multiple records per SiteID.

View 3 Replies View Related

Queries :: Query Not Returning Certain Data

Oct 17, 2014

I have 2 tables, one called Location and the other called PC Inventory. They both have columns called sites. The query is set to search both tables by site name and return the PC's at that site that match the search criteria.The search form has a drop down menu with all the site names. When you choose a site name from the last, the after update event does a re-query to a subform on that same form. The source object of the subform is set the query.[Forms]![PC Search]![Combo6] - this is the criteria on the query that point to the drop down menu.

Private Sub Combo6_After Update()
Me.Child19.Requery
End Sub

This is the requery that point to the subform (child19)..The problem is that some selections from the list don't return any results to the subform. But there is clearly records that meet the criteria in the table. Example: ABC @ Maryland returns all the PC's at the site. DEF @ Maryland doesn't return any PC's at the site, but there are records with that site name.Even if go into the table copy the site name that isn't working and enter into the search field it doesn't return the results.

View 3 Replies View Related

Queries :: Query Not Returning New Data

Jun 30, 2015

Any new data I add to my database isn't showing up when I run a query.I've noticed it on 2 queries. One which is my ChargerSearch query with the field name:

Code:
ChargerSearch: "Charger ID: "+[Charger_ID]+" "+"Scheme Contract: "+[Scheme_Contract]+" "+"Nissan Serial Number: "+[Post_Serial_Number]+" "+"Client: "+[Client]+" "+"Site: "+[Site]

and the Criteria:

Code:
Like "*" & [Enter Search Term] & "*"

And my JobHistory Query which has multiple fields from many tables. With criteria only on the Charger_ID field "[Enter Charger ID]". Both work fine for data that was already there but any new data I've added isn't returning any results when I enter the correct values in the parameters. The data is definitely there in the appropriate tables, it just won't find it on the queries.

View 11 Replies View Related

Queries :: Append Query Returning Too Many Rows

Nov 14, 2013

I want an append query to create a new record in a table and populate that record with fields from a form. Some of these fields are free text, others are from combo boxes.The append query adds vastly too many records (albeit all populated with precisely the same information) - and the number it adds seems to depend on which combo box options I choose.

I have a similar append query which pulls information from earlier records in the table, and combines that with free text and combo boxes on a near-identical form, which works fine and always just adds one row.

View 10 Replies View Related

Queries :: Query Returning Blank Columns

Jun 24, 2013

I've set up a simple query that returns 6 columns of data.This query then shows on a sub-form elsewhere in my DB.The problem is that the query always appears with a horizontal slider. Allowing the user to scroll across to see the other columns in the query...The problem here is that there ARE no other columns of data. They are just empty. I want to restrict the output of this query to ONLY show the 6 columns that i have specified.

I have tried deleting the columns in Query Designer, then save the Query. But every time i re-open it half a dozen or so blank columns are stuck on the end.

View 4 Replies View Related

Queries :: Query Returning 2 Records When Only One Is Expected

Aug 7, 2015

I'm running a query from two related tables in the database.the table relationships and the query design. Instead of doing what I want it to do/what I think I'm asking it to - which is show the sum total number of weeks on the program for each student - it shows me the number of weeks for that student just for that claim, not the total for the student overall. I've added some 'dummy data' (2 records in the claim table relating to 1 student), and the query then returns the student twice in the results.

View 1 Replies View Related

Queries :: Query Returning Wrong Results?

May 7, 2014

I have two tables Contacts & Allowance. There is a relationship defined between the two based on Contact ID (primary field/contacts - foreign key/allowance). The Contacts table has Firstname + Surname. I have produced a query based on certain fields in both tables however the result is totally pulling incorrect info.

View 11 Replies View Related

Queries :: Correlated Query Returning Only 1 Record And Repeating

Aug 10, 2014

I have SQL query/dual sub-query in MS Access that is returning data from the left side of the query FROM correctly, but is only returning one record from the right side of the query FROM. Furthermore, it repeats the display of the one record and it repeats the entire results set with a different one record each time until all the records have been displayed. I expect that problems described as "Furthermore" will not exist by fixing the one record issue. I have tried using all the join types available in MS Access, but none change the result.

The desired output is:
Yellow Blue
11/23/2013 11/19/2013
11/19/2103 10/01/2012
10/01/2102 10/08/2010
10/08/2010 12/14/2007

The actual output is:
Yellow Blue
11/23/2013 11/19/2013
11/19/2103 11/19/2013
10/01/2102 11/19/2013
10/08/2010 11/19/2013
11/23/2013 10/01/2102
11/19/2103 10/01/2102
10/01/2102 10/01/2102
10/08/2010 10/01/2102

The same pattern is repeated 2 more times with Blue values of 10/08/2010 and then 12/14/2007.

Here is the SQL:

SELECT Long_List.Yellow,Short_List.Blue
FROM
(
SELECT DISTINCT BirthDate AS Blue
FROM (
SELECT DISTINCT BirthDate FROM citizens
UNION

[Code] ....

View 11 Replies View Related

Queries :: Nested IIF - Returning Calculated Results In A Query

Oct 30, 2013

What is the best approach to returning calculated results in a query. I have been using nested IIf statements that include DateAdd but I think that I am at a point where there must be a better way.

I want my query to calculate a date based on:
1) A Type field from my table
2) A calculated date based on other query fields.

My query has the following fields:
[Type] [Action 1] [Projected Action 1] [Projected Action 1 Revised] [Action 2]

I want to create a calculated field for [Projected Action 2] that says:

If [Type] is "A" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 10 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 10 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 10 workdays

[Code] ....

I would like to keep using nested IIf but I keep running into problems and I thought that there is probably a better way.

View 7 Replies View Related

Queries :: Select Query With Joins Returning Duplicates

Sep 18, 2014

I am building a select query which is grabbing data from multiple tables with items being linked by a unique field "Certificate_ID".

I have created joins between table A and Table B, and Table A and Table C, linking both by Certificate_ID

All have the join property set to select all Records from table A, but only those from Table B and Table C where the joined fields are equal.

Table A has 5000 records. Am I correct to assume that my query should only return a max of 5000 records as well? When I select Certificate_ID from A and another column from B it only gives me the 5000 unique records. When I add in a column from Table C it is however returning something like 7500 records, with several being duplicates with the same data in every column.

Why it is choosing to duplicate records and give me more than I want. I am sure I am overlooking something simple.

View 1 Replies View Related

Queries :: Prototype Database - Query Returning Results Twice

Dec 30, 2014

I am building a prototype database and currently has two records in it (I know for certain they are not duplicates). Attached is the relationship and below is the query. The query keeps returning the correct results but twice. (Duplicate results.)

SELECT DISTINCT WeekdayName([Weekdaynumber],False,1) AS Weekday, [Master Data].Date,
[Master Data].Machine, [Master Data].Shift, [Master Data Query].[Master Data_Product Code],
[Master Data Query].[Cases/Pallet], [Master Data].[Expected Pallets to Produce],
[Master Data].[Actual Pallets Produced], [Master Data].[Damage (Units)],

[Code] .....

View 6 Replies View Related

Queries :: DLookup And Variables - Query Returning Same Value For All Rows

Aug 8, 2013

I am having a problem with a Dlookup query. I want to achieve the following - I have 2 different tables

Demography Table - consisting of 2 fields, City and Region

For example
City Region
NYC NAM

The other Table totalflow consists of several field, one of the is flow from.

For example
Flow from
NYC
WAS
SEA

I want to do the following. Lookup the Flow from filed in table totalflow, and compare it to City in demography Table. If City = Flow from, return the value in Region. All are text fields.

I have tried following Query, but it only returns the same value for all rows. (from SQL view)

SELECT DLookUp("[Region]","Demography Table","[City] = '" &[Flow from]& "'") AS test
FROM [Demography Table], flowsize;

View 3 Replies View Related

Queries :: Nested Query - Left Outer Join Not Returning All Records

Oct 1, 2014

I have a simple nested query that is not working as expected. My inner query returns 102 records but when I run with outer query I only get 96 records. Below is my query, I don't really want to pull the same fields from both tables but I was doing to test. The values that are missing are those that don't exist with the monthenddate 8/31/2014 - a left join should fix that but doesn't seem to be working ..

Code:

Select distinct a.entity, a.gl_account,a.profit_center,[Open Items_1].profit_center,[Open Items_1].gl_account,[Open Items_1].entity
from(
SELECT DISTINCT [Open Items].entity, [Open Items].gl_account, [Open Items].profit_center
FROM [Open Items]
)a
left outer JOIN [Open Items] AS [Open Items_1] ON
(a.profit_center = [Open Items_1].profit_center) AND (a.gl_account = [Open Items_1].gl_account) AND (a.entity = [Open Items_1].entity)
Where ([Open Items_1].MonthEndDate=#8/31/2014#)

View 1 Replies View Related

Queries :: Parameter Query Returning Incorrect Results Based On Checkbox

Jul 31, 2013

I have a parameter query that contains information on a list of people and contains 3 checkboxes: alumni, parent, business

In this query, I am trying to use parameters to filter the results based on these three fields i.e.

true, false, true would return all records where either alumni, business or both are true, and parent can be either true or false.

false, true, false would return all records where only parent is true, and the other fields do not matter.

View 2 Replies View Related

Nz Returning Text Instead Of Number

May 24, 2007

hi all,

here's the dealio... i need perform an aggregate funtion off of a certain field, but it contains null values. i tried using Nz and an Iif IS NULL statement to get rid of the nulls. but both methods return the values as text. obviously, i can't get an Avg of these text values.

here's the two methods i tried:

Format(IIf([AvailTab].[Per00] Is Null,"1",[AvailTab].[Per00]),"Percent") AS Per01Test

Format(Nz([AvailTab].[Per00],"1"),"Percent") AS Per00

how can i return the data as a number?

thanks,
mj

View 4 Replies View Related

Returning A Sequential Number

Mar 11, 2008

I have a form on which I need to print a sequential number - increasing by 1 each time the form is printed. I cannot think of a way to do that - perhaps something returned from a query? Any suggestions?

Robert

View 3 Replies View Related

Modules & VBA :: Returning Correct Number Of Records

May 3, 2014

I am having trouble with the below returning the correct number of records, and can't see why.

I have one table, tblDevice, which has 4 columns, ID | DeviceRecNo | ExcludeFromCheck | StockLocationID

ID - Autonumber
DeviceRecNo - Number
ExcludeFromCheck - Number (1 = yes, 0 - No)
StockLocationID - Number

I have the following running as part of some code, but it is not returning the correct number of records, and I cant see why not. I have tried creating this in a query in Access itself, and copy the SQL into VBA and it still doesn't return the correct number records.

I have 4 records in there for testing, all with StockLocationID = 3, all with different DeviceRecNo, and two each of ExcludeFromCheck, 2x0 and 2x1

Code:
Public Function test()
Dim db As dao.Database
Dim rs1 As dao.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT DeviceRecNo FROM tblDevice WHERE StockLocationID = 3 AND ExcludeFromCheck = 'No'")
Debug.Print rs1.RecordCount
End Function

View 3 Replies View Related

Queries :: Parameters To Query String

Aug 15, 2014

Do access VBA implements parameters passed to query strings in all following parameters?I've been working in ASP.NET/Razor C# and this would be an example of how it would be done:

Code:
db.Query("INSERT INTO threads (name, date_of_creation, user_id, area_id, user_group_id)" +
" VALUES(@0, @1, @2, @3, @4)",
Request["txtThreadTitle"],
DateTime.Now,
Session["user_id"],
area_id,
0
);

View 6 Replies View Related

Queries :: Possible To Query A Field For Special Characters In A String?

Oct 11, 2013

Is it possible to query a field for special characters in a string? I want to find any records that have characters other than A-Z, 0-9

View 9 Replies View Related

Queries :: Query Will Not Read String From (dummy) Textbox

Aug 25, 2013

I have a two-column list box where the user selects multiple Test Names and Test Measures. Through VBA, I loop through and create a string of the selected items and store into two seperate variables, one for each column. I concatenate with the "In" and some parenthesis to end up with the following:

In(ELA,MEAP,Star Math)
In(DRA, Math, PercentileRank)

I place each of the In statements into two seperate dummy text boxes on the form. Then I point the query criteria to these text boxes.

When I run the query, I get nothing. However, if I copy and paste the In statements above from the text boxes directly into the query criteria, I get the desired results.

I changed the code to create an "Or" statement (e.g. "ELA" OR "MEAP" OR "Star Math"), but still the same issue.

why the query will not read from the text boxes on the Form?

View 4 Replies View Related







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