multiple conditions in a join, access doesn't like on (table1.field=table2.field and table1.field=table3.field)
Hi I have written the following query and am having problems with the
last outer join in ms access... it doesn't like the multiple
conditions that I have in there, if I have just one or the other its
OK but not both... and I have tried putting the second condition in a
where clause but then i don't get an outer join
i'm pulling my hair out on this one... i would be extremely greatful
for some ideas...
SELECT ro.thickness,ro.width,ro.grade,sum(ro.pcs) as pieces,
FROM(((NewOrders as RO
LEFT O...Linking A Text Field With A Number Field
Is there any way to link a field I'll call StudentId in Table1 with
StudentId in Table2? In Table1, StudentId is a number and in Table2
it's a text field. The values match up fine so other than one being a
number and the other a text field, everything else would be okay. I
know I can create an append query to copy, say, Table2 to e.g. Table3
and make StudentId a number field in Student3 but wondered if there's
a more "elegant" solution.
On Mar 6, 11:42=A0am, Sheldon <SHPsalm...@aol.com> wrote:
> Is there any way to link a field I...How to convert Number Field to Text Field
I'm newbie in Filemaker, currently I'm using Filemaker pro v 6.01 on
Windows XP. I have a small problem. I'm trying to make a solution for
Hotel Rate. I want to change the USD. 0.00 to "Included" for breakfast
rate which already included in the room rate.
Anybody can help, will be much appreciated.
"Adhie" <firstname.lastname@example.org> wrote in message
> I'm newbie in Filemaker, currently I'm using Filemaker pro v 6.01 on
> Windows XP. I have a small problem. I'm trying to make a ...MS Qry and text field to date field
I am using MS Query to pull files (from IBM AS400) and put them in
Excel. However, when I do this the dates are now text fields. The text/
Date field looks like this: 20070331 (YYYYMMDD)
I would like the change this text to a date field so Excel will see
it as a date.
Can someone please help me? I have looked all over the place for a
solution and I have found nothing.
Also, if you know of a good place to get information on MS query
please let me know.
> I am using MS Query to pull files (from IBM AS400) and put them in
> Excel. However, when I do this the...Sorting numbers in a text field
Whilst I am sure this question has been asked in other forms before, I
have searched the groups using google and not found an answer. This is
my first question to this newsgroup, but not my first newgroup post to
I have a VARCHAR2 field which contains numbers. Order by sorts these
lexigraphically (how /do/ you spell that?!). I have overcome this with
the code below:
(SELECT MAX( LENGTH(text_col) ) from table),
' ') AS Sorted
ORDER BY Sorted;
This works great for only numbers, however some of my numbers contain
suffixes, as can be seen in the example output below:
10 rows selected.
I am looking for a purely sql hint, or some direction, and am using
Thank you in advance,
I have found my own answer, thank you.
Please see code below (note that the +2 on the default in the decode is
because there is only ever a hyphen and letter, 2 characters. if you
had a variable no of characters afterwards this will not work)
Thank you for any who looked over it
COLUMN Sorted format a20
SELECT DECODE( INSTR(test,'-'), --test for hyphen
0, LPAD( test, --if no hyphen, add padding
to right align text/numbers
...Sorting Numbers in a Text Field
One field in my database contains the dosage of medicines. It's a text
field since I have to include the units.
Here's a list of what might be in the field:
When I sort I want them to appear as I listed them above. However, they
Any suggestions for solving this problem?
This question has been answered multi times before. You have entered the
numbers in a text field so they are sorted on their ascii value and not on
their numeric value. Put the numbers in a number field, leave the mg part
out (since it is always mg...Formatting number fields within a calculated text field
I have a calculated text field which is part of an invoice printout.
The definition is as follows:
Case ( WeeklyRental > 0 ; "Includes " & Weeks & " weeks rental. After "
& Weeks & " weeks rental will be charged at �" & Round (WeeklyRental;2
) & " per week"; "" )
This shows in an invoice to specify rental amounts after a certain
number of weeks. I want this statement to completely disappear in other
invoices where no rental is to be charged. As it is it works fine
except - the weekly rental does not format properly...Accessing text form fields name=field[label]
and changing those examples to suit my needs. That said ....
..... I have some select fields in a form I created for a database search
that I am unable to figure out how to access. (The search is
implemented in Php/MySQL.)
The user enters search values for: name, address1, city, .... etc.,
..... and for each of these they also select whether the search should
look for the field to be equal to, not equal to, Like, or Not Like the
value in the database.
So in the form I have:
<input type=text name=city&...text field only for text
I would like some help about the following :
I have a text field and I don't want it contains any numbers. How can I
this situation ?
So far, I couldn't find literature exacly about this here in the newsgroup.
"Paulo Rodrigues" <email@example.com> wrote in message news:<firstname.lastname@example.org>...
> I would like some help about the following :
> I have a text field and I don't want it contains any numbers. How can I
> this situation ?
> So far, I couldn't...Text field calculation to include a Number field with thousands separator
No doubt I'm missing something quite basic here but a silly little problem
that I can't find a solution to.
Using FM8.5 Adv on Win XP.
I have a number field called rem_charge, which appears on a layout, it is
displayed as a number with 2 decimal places and a thousands separator when
the value is 1000.00 or greater - all ok.
Also on this layout I have a calculated text field called rem_chargetext,
this field I have calculated as
"�" & rem_charge & " plus VAT"
I want the rem_charge part of this displayed with the thousands separator as
required. Unfor...how to sort with text and numbers in one field
I started working on a new database for our fire alarm shop. The
idea was to keep track of all the devices (smoke detectors, pull
stations, etc) as well as how many different makes and models of fire
alarm panels that we maintain. It would also be there to answer the
question that comes up at least once a year: "Exactly how many smokes
(or pull stations) do we have in the field?" The boss has also
requested that one of the forms to create would be an annual test form.
This would allow the techs to find the building they need to test in
the database, and then print up all the ...SQL Server View integer field shows as text field in Access
I have an SQL Server View where my field in the view is an integer
field and works well. When I link to it from Access, the field is
defined as a text field in Access.
Does anyone have any idea why this is happening or how to fix it?
Thanks in advance!
On Sun, 3 Aug 2008 20:31:43 -0700 (PDT), kevinfbutt
That's quite unusual. Can you post the SQL for that field in that
What version of Access and SQL Server? Fully patched?
Can you replicate the problem with a view in one of the SQL Server
Microsoft Access ...Access treating Memo like a Text field
I have an web app and I am using access 2002 and VB.net. I am using a
data adapter to fill a dataset.
I am retrieving product descriptions, some are long and i have used a
field of memo datatype to save them in.
The problem is sometimes I can only retrieve the first 255 characters
in a memo field and other times I can retrieve the full product
descriptions which can be over 640 charaters.
The problem is consistent, the same fields are always truncated to 255
characters and the others are always displayed in full.
Has anyone heard of this before? Any ideas on where I can look?
Thanks ...SQLExecute Update to MS-ACCESS text field
I'm trying to update an access .mdb table1 field test, a text field and am
getting the resulting error below. Any ideas. TIA
SQLExecute[db,"UPDATE table1 SET test = "<>v13<>" WHERE rec = "<>ToString[recV]];
JDBC::error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '16-5'.
...Parsing out data on text field in MS ACCESS
A newbie here having a hard time figuring out how to parse out the
City, State and Zip from a text field. I have a text field called
"Registration" with a size of 40. In this field there are city, state
and zip information. I need to seperate all three items into their
own respective fields. Unfortunately, the information varies in size
and location. An example, would be:
"LOS ANGELES CA90063 ", while another record would be
"LOS ANGELES CA90063".
If anyone can point me to any references or examples on the web, it
would be greatly app...text fields to memo field
Is there a way to combine text fields into one memo field?
This is an existing database. Some people in the database may have several
"contact" entries where they have a "note" in a text field. I would like to
figure a way to combine all the "note" fields for each person into one memo
"RR" <email@example.com> wrote in message news:<S6HRb.firstname.lastname@example.org>...
> Is there a way to combine text fields into one memo field?
> This is an existing database. Some people in the database may have seve...How to link fields in MS word document to Access database
Anyone have experience in linking fields in an MS Word document to an
What I am considering doing is taking an existing fill-in-the fields
word document, for which I cannot now save the data, and allow it to be
opened with no data, have the fields filled in and then save the data in
an Access database. I also want to later select the document (from a
file in the database), open the word document and populate the fields
from the database. Of course if there are then any updates I want to
put them back into the database.
Anyone with sample code?
Bob Alston...Problem concatenating memo fields in MS access database
I have an access database form where I have a button that is supposed
to be appending the contents of 1 memo field to another is behaving
non-predictably. The code is:
Me![ToDo] = Me![ToDo] & Chr$(13) & Chr$(10) & Me![Studies1] &
Chr$(13) & Chr$(10) & Me![Studies2]
Me![Meds] = Me![MedsEntered] & Chr$(13) & Chr$(10) & Me![IVF] &
Chr$(13) & Chr$(10) & Me![Diet]
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
Any thoughts on what I'm doing wrong? Could there be a casting problem?
(I just want to append the text...FileMaker Pro 9 OS X Defining text fields and numerical fields to aviod the quote in the fields to avoid it from exporting out to an Excel Spreadsheet or Text File.
I have been reading the help files in FileMaker Pro 9 about Text
formats but have yet to find my solution, perhaps there is not one or
it is a formula that is beyond my scope at this time.
I have fields that are strictly numbers and the fields have been
defined for numbers and I have no " around the text.
I have field I have defined as text for it is a mix of text and number
or text and non text characters such as / or ? or . or , or * and the
like and they get " " in the field. I want to prevent that.
How do I do this where none of my input gets the open and closing
double ...multi-field array sort using Sort::Fields method
Hello all. My goal is to be able to perform a "multi-field sort on a
multidimensional array". Having read many posts in the newsgroups, I was
unable to find a "straight" answer to this problem. I therefore came up
with this method. My question is, is there a more efficient solution to
this problem or is my method acceptable? Is there another CPAN module that
can be used? I welcome all opinions.
my ( @arr01, @arr02, $r, $c, $string, $aref, $delim, @arr_temp,
@arr01 = ( [1, 'a', 'dom'],...Regular expression for email or mobile number in one field like facebook email/mobile field in registration
Regular expression for email or mobile number in one field like facebook em=
ail/mobile field in registration I am building a registration form in in wh=
ich i am trying a make a field say email/mobile where user can either put e=
mail address or mobile number with country code.similiar approach is used i=
n facebook registration.can anyone have solution for such regular expressio=
n. i am building the form using angularjs with its default validator.
> Regular expression for email or mobile number in...Forcing a New Line in a Text Field in MS Access using VB
I feel I'm beating an old horse to death with this question but I
can't get around the problem.
I want to update a text field on a form with a string from a VB
module. I need enter/linefeeds in the string.
Chr(10) & Chr(13)
Chr(13) & Chr(10
none of which seem to work.
I've set the properties for the text box to [Enter Key Behavior] =
'New Line In Field' and [Can Grow] = 'yes'.
At the moment, my code looks like this:
celsql = celsql _
& "Program: " & encname _
...sort items like Netflix
Hello, I would like to be able to have the user sort a list of items
similarly to the way you sort your queue on Netflix.com. (the numbers
dont change dynamically on netflix, they must be doing something
serverside with them)
Like this: ( represents a text or select filed)
 an item
 something else
 a differnt thing
 the other
So, the user would change the 5 to a 1 if he wanted that item to be #1.
I thought that if i had script that would move everything down a
number each time the user changed one of them, that would work. The
items can stay where they ar...how to import text field from MySQL db to MS Access 2003 using ImportXML
I'm trying to import data out of MySQL db to MS Access 2003 using
ImportXML. I don't have any problems doing the import, except for one
table with a "text" field defined. I'm getting an error message saying
"The field is too small to accept the amount of data you attempted to
add. Try inserting or pasting less data.".
Do you have to use ImportXML? Why can't you use the ODBC driver to MySQL?
<email@example.com> wrote in message
> Hi Guys,