Question about an SQL statement

I have an SQL statement that is way to long.
bd="days"
bn="nights"
ds="day shift"
 and so on.

stwhere1 = "SELECT [shift],[lname],[fname],[locat1]" & _
            " FROM [tbl_Roster]" & _
            " WHERE [shift] = '" & bd & "' or [shift]='" & bn & "' or [shift]
='" & ds & "' or [shift]='" & dn & "'or [shift]='" & aft & "'"

The above WHERE is on one line and it works.

I was wondering if I could use something like this to make it more readable?

 " WHERE [shift] = '" & bd & and & bn & and & ds & and & dn & and & aft & "'"

Thanks for your help. It is much appreciated.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201003/1

0
Afrosheen
3/18/2010 1:04:19 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

3 Replies
835 Views

Similar Articles

[PageSpeed] 30

" WHERE [shift] IN ('" & bd & "', '" & bn & "', '" & ds & "', '" & dn & "', 
'"& aft & "')"


-- 
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Afrosheen via AccessMonster.com" <u46942@uwe> wrote in message 
news:a5315b9778b9b@uwe...
>I have an SQL statement that is way to long.
> bd="days"
> bn="nights"
> ds="day shift"
> and so on.
>
> stwhere1 = "SELECT [shift],[lname],[fname],[locat1]" & _
>            " FROM [tbl_Roster]" & _
>            " WHERE [shift] = '" & bd & "' or [shift]='" & bn & "' or 
> [shift]
> ='" & ds & "' or [shift]='" & dn & "'or [shift]='" & aft & "'"
>
> The above WHERE is on one line and it works.
>
> I was wondering if I could use something like this to make it more 
> readable?
>
> " WHERE [shift] = '" & bd & and & bn & and & ds & and & dn & and & aft & 
> "'"
>
> Thanks for your help. It is much appreciated.
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201003/1
> 


0
Douglas
3/18/2010 1:12:31 PM
hi,

On 18.03.2010 14:04, Afrosheen via AccessMonster.com wrote:
>   " WHERE [shift] = '"&  bd&  and&  bn&  and&  ds&  and&  dn&  and&  aft&  "'"
>
> Thanks for your help. It is much appreciated.
Maybe you have an normalization issue. Do you need to query all shifts 
or do your shifts have something in common?

I would consider using a Shift table.

mfG
--> stefan <--
0
Stefan
3/18/2010 1:23:22 PM
Thanks for the quick reply and help again Doug. It work like I wanted it to.


Douglas J. Steele wrote:
>" WHERE [shift] IN ('" & bd & "', '" & bn & "', '" & ds & "', '" & dn & "', '"& aft & "')"
>
>>I have an SQL statement that is way to long.
>> bd="days"
>[quoted text clipped - 17 lines]
>>
>> Thanks for your help. It is much appreciated.

-- 
Message posted via http://www.accessmonster.com

0
Afrosheen
3/18/2010 1:35:10 PM
Reply:

Similar Artilces:

SQL View duplicating records
I've created a view on inventory transactions linking IV30300 and IV30400, wanting to pull the lot number information into the view for each inventory trx. I have a transaction that was an INVADJ, with one item on it, recorded at 3 different quantities, 3 different costs, and 3 different lot numbers. In my results pane, this transaction is listed 9 times, once for each combination of qty, cost, and lot number. What causes this to happen and how do I change my query to pull only one line per combination of qty, cost and lot number in this transaction? Thanks for the help. Frank Hamelly...

a question 'bout const (design)
hello;-) i've a little complex control-class with it's own worker-tread for controls of that type. most control-setter-functions (for example: set the background color, let an element blink, etc...) work like this: 1. someone calls a public fn. 2. public fn calls private static threadpost-wrapper. 3. threadpost-wrapper post ThreadMsg 4. control class worker UI thread does something stupid... so, the question is: the public fn that calls the wrapper actually *could* be marked const as is just calls static and const fn. On the ohter hand, ive read somewhere that i should try to have ...

X400 requirement question
Hi We have recently migrated from Exchange 5.5 to Exch 2000 and now almost 95% to Exchange 2003. Do we need X400 email addresses in our emails. If not then how can I remove them from our reciepient policies. Regards Ahsan Ahsan Jalil wrote: > Hi > > We have recently migrated from Exchange 5.5 to Exch 2000 and now almost 95% > to Exchange 2003. Do we need X400 email addresses in our emails. If not then > how can I remove them from our reciepient policies. The X.400 email addreses are required for Exchange functionality (exchange internal mail routing is done using X.400 ...

Restore Exchange question
I'm running Exchange 2003 on a Windows 2003 Server. I've setup another network with same domain/AD running Windows 2003 server and installed Exchange 2003 server as well. I restored the exchange store db into this new exchange 2003 on the new network. The restore was successful. However because I didn't restore the windows AD state, I didn't had all the users in the AD. I noticed the mailboxes were not mapped because there wasn't any user in AD. I added few users with same login into the new AD. My question is is there a way to map the mailboxes I restored to the users in...

Odd restore question
Is it possible to backup the information store of a (2003) server and restore it to a new server in a new organization? This is a situation where the server is not validating users due to AD troubles (see "GAL? What GAL?"), and stored e-mails cannot be exported using Outlook or ExMerge; I need to rebuild the whole organization from scratch (the server is not enough, AD is totally screwed), but the users don't want to lose archived e-mails. Massimo On Thu, 15 Sep 2005 21:06:55 +0200, "Massimo" <barone@mclink.it> wrote: >Is it possible to backup the info...

Simple query question 08-05-10
Hi, I'm using SQL Server 2005. What I want is to return a column "venue", but if that column is null, I want to return the value of the column "site" (in either case, I want the column header to be "venue"). How do I construct such a query? Thanks, - Dave You can use expression with COALESCE and alias the expression as venue: SELECT COALESCE(venue, site) AS venue... -- Plamen Ratchev http://www.SQLStudio.com Try this: SELECT COALSECE(venue, site) AS venue FROM my_table -- Gert-Jan laredotornado wrote: > > Hi, &...

Publisher 2003 question #2
I've just installed 2003 where did the red guide lines go? I used to = have both blue and red guide lines in 2002 now I only have the one blue = line (around the page) can I get the red lines back? Tricia After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Tricia <Tricia@home.vic.au>... > I've just installed 2003 where did the red guide lines go? I used to > have both blue and red guide lines in 2002 now I only have the one > blue line (around the page) can I get the red lines back? Microsoft decided that nobody used the pink guides around th...

Report showing #Error on a valid SQL View
I have an Access app with an SQL Server backend. One report is based on a VIEW. When I run the view alone all the data is visible. But when I run the report, I get #Error on one field. The field is numeric. However, the RecordSource property of the textbox is: [MyNumericFieldFromSQLView]/32 What's strange is that when I remove the division by 32, the #Error disappears. What I need to do in the report is divide the value from the view by 32. I cannot modify the view because it is not an option. Any help? Thanks ! It is possible that the field is a text field that is...

office 2003 install question
I just purchased a new HP laptop that does not have an optical drive. I have Office 2003 with my product key and want to know if there is any way to install this on my new laptop without an optical drive. Any help is appreciated. Thanks! "freddy" <freddy@discussions.microsoft.com> wrote in message news:8B1912A3-B7DB-4585-A902-543F04317242@microsoft.com... > I just purchased a new HP laptop that does not have an optical drive. I > have > Office 2003 with my product key and want to know if there is any way to > install this on my new laptop withou...

question abt location of inbox messages
Hi all, Is there a way to save messages from my: 1.inbox 2.sent items 3.del items 4. drafts to a folder on my network instead of my computer ? thanks in advance "Jetzza" <sales@jetzza.sr> wrote in message news:eU%23tW6sxKHA.984@TK2MSFTNGP05.phx.gbl... > Hi all, > > Is there a way to save messages from my: > > 1.inbox > 2.sent items > 3.del items > 4. drafts > > to a folder on my network instead of my computer ? Of course, you can save them anywhere that you want to. If you are asking about moving the 'stor...

SQL server process scheduling from GP 9.0
hi friends, I have created a stored procedure in ms sqlserver. i need to run this process after certain interval which i want to handle from G.P. i.e. i want to schedule this back end process to run after every 1 min. this i want to manage from G.P. 9.0 can anyone help me out thanks in anticipation ...

SQL 2000 to SQL2005 upgrade
We have planned an upgrade from SQL2000 to SQL2005 this weekend, Is there any concerns for Dynamics or anything special I have to do after the upgrade to SQL2005? I understand there is a new ODBC connection I have to install (native client) on each workstation to connect to the SQL2005. Any other setting in SQL2005? thanks -- Doug Security in SQL 2005 is ridiculously tight. I ended up having to re-create all of my user accounts because they didn't transfer over properly when I did the upgrade. I still have users that can't log in to FRX and on the FRX discussion board I&#...

Install problem, can't locate SQL instance same machine
I'm trying to install Action Pack's CRM software on my server in-office. I can't even get the silly thing to find SQL and its the same machine!! What gives!?! The server is Win 2000 AS /SP3 and is a DC, but not any of the FSMO role owners. It also has exchange running it. I go about 3 screens into the install where it asks to select my SQL and create new or use existing databases. I try typing in the UNC for my server and say next, it sits the for about 15 seconds and says failed to authenticate/rights issue. I'm logged in as domain admin, and SQL security is set at Windo...

CRM SQL Jobs
We have been running CRM3.0 in production for 7 months now and we just noticed that the following SQL jobs have been failing: MSCRM.update contract states MSCRM.ftcat_documentindex MSCRM Identity Reseeding MSCRM Index Defragmentation MSCRM Index Reindexing MSCRM Stored Procedure Priming I think it is just because they have been pointing towards the wrong database within SQL. 2 Questions: 1) Is it normal to have these jobs running on SQl for CRM? 2) Which database do I want these to point at? Metabase or MSCRM? Thanks Hi Dave, We use v1.2 and have re...

Mailbox size alerts
Hi, I've got a couple of issues that my MD has asked me to look into, wondered whether anyone on here could help me out :-) Is it possible to send the Mailbox Size Alert message to someone else as well as the mailbox owner? (in this instance I need to send a message to the owner of the mailbox AND their immediate superior) Also is it possible to issue warnings based on the number of e-mails as well as the size? My MD seems to think that anyone with a large amount of e-mails isn't managing their workload correctly! BTW I am running Exchange2003 on Windows2003 Server. Thanks in adva...

How do I run a SQL query against a MS SQL Server database from exc
How do I run a SQL query against a MS SQL Server database from within Excel 2002? I know it can be done somehow using the SQL.Request addin function. However my attempts using this function have continually failed. Can some one please provide a working example to the pubs or Northwind dbs that I can work with and use to help debug my attempt? Thank you! ...

problem posting questions
test any problems posting to this list today? i have tried twice and it is not working. thanks. Mike Better use a Newsreader like OE. Read this from Dave Peterson '*************************************************** You may want to connect to the MS newsgroups directly. Chip Pearson has some notes written by Leonard Meads at: http://www.cpearson.com/excel/DirectConnect.htm David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/xlnews.htm http://www.mvps.org/dmcritchie/excel/oe6.htm Tushar Mehta's notes at: http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.ht...

Outlook 2003 copying messages to folders question
I am using Outlook 2003 and have a question about filing either “incoming” or “sent” mail in any of the folders that I have created. The filing choices in mail are either to “move” or “copy”. If I choose to “copy”, Outlook will create a copy of the email and place it in the folder I have selected. If, for some reason, I forget that I’ve already filed the email in that same folder and do it again, Outlook doesn’t inform me that a copy of that email already exists in the file. As such, it is very possible to have multiple copies of the same email filed in a folder. I would lik...

Not a Vista Question, but...
I have used news forums such as this for years - dating all the way back to Windows 95. I have recently noticed that my favorite Microsoft Word -- and other software specific forums -- are no longer being supported by Microsoft. They contained some brilliant MVP professionals. Has this talent disbanded altogether, or have they re-grouped elsewhere under another banner? Thanks in advance. Gordon Biggar Houston, Texas Microsoft is in the process of killing off its newsgroups. They will = all be killed by October 1 of this year. Its alternative is a web-based = forum ...

Access Deleting a Query SQL
Hi all, I am working with a multiple parameter search query. The form I am using to conduct the search displays the results in a subform. I have gone through and really combed through the SQL to make sure there are no blatant spelling errors or incorrect references within the code. Anyways when I open the form that it is attached to, I keep getting the boxes described in this website http://allenbrowne.com/bug-13.html .. The form is open, and I even changed names of fields in the table in case SQL was choosing to be picky about what I named my fields. Is there anything else that would cau...

Linking a Combo box to variable SQL query in subform
I am in the process of creating a relatively small D'Base that will monitor employee competency. There are several tables and relationships within this, but I don't consider this to be a big project (although my first post-graduation in the real world). I have configured the records to be spread over two tables. One is the tbl_ReviewHearder (containing employee info', review date), the second, tbl_ReviewContent contains the competency information. This is based on a 1-many relationship (1 header, lots of content) I have written VB to filter the combo boxes, but I am having ...

Easy Question
Hi, as noob Exch 2003 poweruser, I search the settings I have to set for this szenario : I have created a domain named "demo.domain.com" with a MAIL (A) and a mail.domain.com (MX) items in our DNS Infrastructur. Further I Installed a SMTP Server and tested with telnet (Port 25) and Windows Mail Tests (send direct to MAIL-Host). All is working properly. Now, the Tests with our "really" Outlook Client isn't working correct, It seams that our Exchange server (mail.domain.com, one Server) try to send outside (our ISP) the mails for "demo.domain.com&qu...

Array saving question (lbound)
Hello! I am saving an array to a file and then load it again. Open sPath For Binary As #iFile1 If uLoad Then Get #iFile1, , uArray Else Put #iFile1, , uArray End If My array looks like this: Redim myArr(1 to 3) as long myArr(1) = 100 myArr(2) = 500 myArr(3) = 10000 When I load it again, I say: Redim MyNewArr(1 to 3) and call the above sub. For some reason, MyNewArr now looks like this: MyNewArr(0) = 100 MyNewArr(1) = 500 MyNewArr(2) = 3 When I try to access MyNewArr(3) an out of bounds error is thrown. Can somebo...

very simple question, but i cant find the answer
Hello, I am trying to write a generic tool that accesses a SQL server and reads/updates/deletes/creates records. I want to reference this tool from my asp.net pages to talk to my db. by the way, i want the results of any read, update and create to be returned in xml. when reading data, i populate a dataset with data and then use the dataset.getxml method to return xml. is this a neat way of reading data? how does it compare to the FOR XML EXPLICIT command in sql server? when creating/updating data i was thinking of using a data adapter but this seems a bit over the top, maybe i am wrong?...

Moving SQL Databases to another drive results in error
I am trying to move the CRM SQL Databases to another drive that has more space and I get a replication error. Is there any documentation to help me move these successfully?? McKenzie This is a multi-part message in MIME format. ------=_NextPart_000_005D_01C5A44F.29F39C90 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: quoted-printable you can do this but you need to disable replication and re-enable = afterwards Use SQL Enterprise manager, select the database you want to move and = then=20 choose the option (after right click) to detach the database,then ...